asked on

Excel to copy data from criteria vba

I have the attached Excel file, and I want to copy all records from the "AmazonExport" worksheet to the "Data" worksheet that have the Order Payment word in the Transaction Column D.

I create the following vba code to search and paste these records, When I run my code I receive the Variable not defined error message...

Sub finddata()
Dim Order_Payment As String
Dim finalrow As Integer
Dim i As Integer
Sheets("Data").Range("A2:I5000").ClearContents
Order_Payment = Sheets("AmazonExport").Range("D2").Value
finalrow = Sheets("Data").Range("A5000").End(x1Up).Row

For i = 2 To finalrow
If Cells(i, 1) = Order_Payment Then
Range(Cells(i, 1), Cells(i, 12)).Copy
Sheets("Data").Range("A2").End(x1Up).Offset(1, 0).PasteSpecial xlPasteFormulasAndNumberFormats
End If

Next i

Sheets("Data").Range("A2").Select

End Sub

Open in new window

Amazon-Export-to-Xero-Sample.xlsm

Martin Liss

There were several things wrong including that you had x1Up (with a one) rather than xlUp.

Sub finddata()

Dim Order_Payment As String
Dim finalrow As Integer
Dim i As Integer
Dim lngNR As Long

lngNR = 2
Sheets("Data").Range("A2:I5000").ClearContents
With Sheets("AmazonExport")
Order_Payment = .Range("D2").Value
finalrow = .Range("A5000").End(xlUp).Row

For i = 2 To finalrow
If .Cells(i, "D") = Order_Payment Then
.Range(.Cells(i, 1), .Cells(i, 12)).Copy
Sheets("Data").Cells(lngNR, 1).PasteSpecial xlPasteFormulasAndNumberFormats
lngNR = lngNR + 1
End If

Next i
End With
Sheets("Data").Range("A2").Select

End Sub

ASKER CERTIFIED SOLUTION

Subodh Tiwari (Neeraj)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

joeserrone

ASKER

Thank you so much for your help!!

Subodh Tiwari (Neeraj)

You're welcome. Glad I could help. :)