copy/paste using VBA

my vba below is copying and pasting data from one sheet to another.
I need assistance in pasting it to the next empty row. I keep getting an error msg when attempting to modify. (the macro below just paste from A2)

Sub Copy()
Dim i As Long
Dim lngLastRow As Long, lngPasteRow As Long

'Find the last row to search through
lngLastRow = Sheets("Data").Range("D65535").End(xlUp).Row

'Initialize the Paste Row
lngPasteRow = 2

For i = 2 To lngLastRow
    If Sheets("Data").Range("D" & i).Value = "test1" Then
        Sheets("Data").Select
        Range("A" & i & ":IV" & i).Copy
        Sheets("Cars").Select
        Range("A" & lngPasteRow & ":IV" & lngPasteRow).Select
        ActiveSheet.Paste
        lngPasteRow = lngPasteRow + 1
    End If
Next i

End Sub
Gary LensProject ManagerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Please try something like this...

For i = 2 To lngLastRow
    If Sheets("Data").Range("D" & i).Value = "test1" Then
        Sheets("Data").Range("A" & i & ":IV" & i).Copy Sheets("Cars").Range("A" & Rows.Count).End(3)(2)
    End If
Next i

Open in new window



Otherwise instead of looping through the range on Data Sheet, you may use Autofilter to filter the data on column D for the criteria "test1" and copy and paste the filtered rows only.
Also, you should avoid selecting the sheets and ranges unless really required.

Sub Copy()
Dim wsData As Worksheet, wsCars As Worksheet
Dim i As Long
Dim lngLastRow As Long

Application.ScreenUpdating = False

Set wsData = Sheets("Data")
Set wsCars = Sheets("Cars")

'Find the last row to search through
lngLastRow = Sheets("Data").Range("D65535").End(xlUp).Row

With wsData.Rows(1)
    'Autofiltering the column D for the criteria "test1"
    .AutoFilter field:=4, Criteria1:="test1"
    
    'Checking if Autofilter returns any row
    If wsData.Range("A1:A" & lngLastRow).SpecialCells(xlCellTypeVisible).Cells.Count > 1 Then
        'Pasting the filtered rows into the Cars sheet in the next empty row
        wsData.Range("A2:IV" & lngLastRow).SpecialCells(xlCellTypeVisible).Copy wsCars.Range("A" & Rows.Count).End(3)(2)
    End If
    .AutoFilter
End With
Application.ScreenUpdating = True
End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Gary LensProject ManagerAuthor Commented:
wow! that works best. how would I be able to add another criteria using the autofilter option?

Example: I would want to add test2 in the criteria as well.
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
For that, replace the line#16 with the following line...

.AutoFilter Field:=4, Criteria1:="=test1", Operator:=xlOr, Criteria2:="=test2"

Open in new window

0
Gary LensProject ManagerAuthor Commented:
thank you sir!
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome Gary!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VBA

From novice to tech pro — start learning today.