Copy And Paste Rows

[Glenn Robertson]

Hi,

I have created this code which autofilters column a on
sheet 1 for the name which you input in the input box.
I then want it to copy all the rows on sheet1 that have
information in it a paste it onto sheet3 at the first
available row.

I have written the code below but when I run it. I get the
following message :-

Run-Time Error '1004'
The Command you chose cannot be performed with multiplt
selection. Select a single range & click the command
again.

Here's my code, please can someone tell me where I'm going
wrong.


Dim Name1 As String
Dim lastrow As Long
lastrow = Worksheets("Sheet1").Range("A65536").End
(xlUp).Row
Name1 = InputBox("Please Enter Name: ", "name1")
Worksheets("Sheet1").Range("A:B").AutoFilter Field:=1,
Criteria1:=Name1
Application.DisplayAlerts = False
Worksheets("Sheet1").Range("A1:B" &
lastrow).SpecialCells(xlCellTypeVisible).Cut
Worksheets("Sheet3").Range("A65536").End(2).Paste
Application.DisplayAlerts = True
Application.CutCopyMode = False
Worksheets("Sheet1").Range("A1").AutoFilter
End Sub

Thanks

 

[Tom Ogilvy]

This worked for me:

Sub Tester1()
Dim Name1 As String
Dim lastrow As Long
lastrow = Worksheets("Sheet1").Range("A65536").End(xlUp).Row
Name1 = InputBox("Please Enter Name: ", "name1")
Worksheets("Sheet1").Range("A1").CurrentRegion. _
Resize(, 2).AutoFilter Field:=1, _
Criteria1:=Name1
Set rng = Worksheets("sheet1").AutoFilter.Range
Set rng = rng.Offset(1, 0).Resize(rng.Rows.Count - 1)
Application.DisplayAlerts = False
rng.Copy Destination:= _
Worksheets("Sheet3").Range("A65536").End(xlUp)(2)
rng.Delete
Application.DisplayAlerts = True
Application.CutCopyMode = False
Worksheets("Sheet1").Range("A1").AutoFilter
End Sub