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
joeserroneAsked:
Who is Participating?
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.

Martin LissOlder than dirtCommented:
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
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
The correct way to achieve this is like this.....
I have inserted two button on each Sheet one called Copy To Data on AmazonExport Sheet and another called Copy From AmazonExport on Data Sheet. You may click on any of these buttons to achieve this task.

When you copy the data from one sheet to another sheet, its always good practice to qualify the range with the Sheet Name so the code will work irrespective of which sheet is active during the code execution and you get the similar output in any case.

Here is the code and attached workbook for details.....
Sub FinalData()
Dim Sws As Worksheet, Dws As Worksheet
Dim Slr As Long, Dlr As Long
Dim Srng As Range
Application.ScreenUpdating = False
Set Sws = Sheets("AmazonExport")
Set Dws = Sheets("Data")
Slr = Sws.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Dlr = Dws.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
If Dlr > 1 Then Dws.Range("A2:I" & Dlr).Clear
Set Srng = Sws.Range("A2:I" & Slr)
Sws.AutoFilterMode = 0
With Sws.Rows(1)
        .AutoFilter field:=4, Criteria1:="Order Payment"
        Srng.SpecialCells(xlCellTypeVisible).Copy Dws.Range("A2")
        .AutoFilter
End With
Dws.UsedRange.Columns.AutoFit
Application.CutCopyMode = 0
Application.ScreenUpdating = True
End Sub

Open in new window

Amazon-Export-to-Xero-Sample.xlsm
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
joeserroneAuthor Commented:
Thank you so much for your help!!
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome. Glad I could help. :)
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
Microsoft Excel

From novice to tech pro — start learning today.