Paste Rows

Location: http://www.mvps.org/dmcritchie/excel/pasterows.htm Home page: http://www.mvps.org/dmcritchie/excel/excel.htm [View without Frames]

Paste Rows from one sheet to another

Option Explicit
Sub Ken01()
  'Copy all rows with column 1 matching value
  ' of selected cell to next available row
  ' in sheet named with value of matching cell  2000-06-09
  ' 2007-04-25 create sheet if needed, return to original sheet
  Dim I As Long
  Dim original As Worksheet
  Application.ScreenUpdating = False
  'On Error Resume Next
  Dim mrow As Long
  mrow = Cells.SpecialCells(xlLastCell).Row
  Dim ThisText As String
  Dim Str1 As String
  Dim Row As Long
  ThisText = ActiveCell.Value
  'ThisText = InputBox("Supply Name to copy", , ThisText)
  'MsgBox "You chose" & ThisText
  If ActiveSheet.Name = ThisText Then
     MsgBox "You can't start from a sheet named " & ThisText
     Exit Sub
  End If
  Set original = ActiveSheet
  For I = 1 To mrow
    If Cells(I, 1) = ThisText Then
       Str1 = Str1 & "," & I & ":" & I
    End If
  Next I
  Str1 = Mid(Str1, 2, 2000)
  Range(Str1).Copy
  'Sheets("Bob").Activate
  On Error Resume Next
  Application.DisplayAlerts = False
  Sheets(ThisText).Activate

  If Err.Number <> 0 Then
    If Err.Number <> 9 Then MsgBox Err.Number & " -- error "
    '-- see http://www.mvps.org/dmcritchie/excel/sheets.htm
    Sheets.Add After:=Sheets(Sheets.Count)  '-- place at end
    'Rename current Sheet
    ActiveSheet.Name = ThisText
  End If
    Application.DisplayAlerts = True
  On Error GoTo 0
  If [A1].Value <> "" Then
    Cells(1, 1).End(xlDown).Select
   Row = ActiveCell.Row
   Range(Cells(Row + 1, 1), Cells(Row + 1, 1)).Select
  End If
  ActiveSheet.Paste
  original.Activate    'Return to original worksheet
  Application.ScreenUpdating = False
End Sub

Cell A13 containing "Bob"
is selected (boldface for
display only).

	A	B
11	Bob	$10.07
12	John	$12.36
13	Bob	$18.49
14	George	$12.36
15	Bob	$14.89

Sheetname: Bob

	A	B	C
1	A1	B1	C1
2	A2	B2	C2
3	A3	B3	C3
4	A4	B4	C4
5	A5	B5	C5
6	A6	B6	C6
7	Bob	$10.07
8	Bob	$18.49
9	Bob	$14.89

Alternate approaches

A non macro example would be to use filters, allowing you to print or copy only filtered cells.

Insert another row above your first row.
A1: Name
B1: Amount

Select row 1
Data --> Filter --> Autofilter

Click on arrow in Name column header (cell A1) ChooseBob Copy the area showing data (ctrl+c) Insert new worksheet (Edit --> Insert worksheet) Paste (ctrl+v) intot the new worksheet

Another possibility, simply select all of data and sort on Column A.

To loop through the sheets collection

Some examples "for each sht in " can be found in sheets.htm, buildtoc2.htm, and insrtrow.htm among others. you can find using my search page.

Related Examples

(placeholder)

Related Information on other sites

(placeholder)

You are one of many distinguished visitors who have visited my site here or in a previous location since created on June 10, 2000.

[My Excel Pages -- home] [INDEX to my site and the off-site pages I reference]
[Site Search -- Excel] [Go Back] [Return to TOP]

Please send your comments concerning this web page to: David McRitchie send email comments