copy new data to another worksheet as long as it matches criteria

[excelforfinance]

Hi all. Hoping all you excel experts can help with this Data matching worksheet Im trying to create.
I want to copy values in one worksheet to another worksheet as long as it matches two criteria.

Picture my worksheet having two sheets, one sheet for data entry that will get replaced with new data every month, and another sheet that stores the incoming data in the first sheet.

I want to copy the data to another worksheet but it needs to match the account code that are in the rows and need to match the dates that are in the columns. For example, in the data entry worksheet, the first column will have account codes ABC with an amount $100 and EFG with an amount of $200 for February 2020. I want a macro that will look in other worksheet and find ABC in rows and February 2020 in columns and copy and paste values $100 and find EFG and Februray and copy and paste values $200.
Is this something you all can help with? Much appreciated!

 

[excelforfinance]

Re: copy import data to another worksheet as long as it matches date and account #

Guys - i gave the code a try and hoping someone here can debug or help me figure out the right approach.

Sub CopyImportData()
Dim lastRw1, lastRw2, nxtRw, m, datem


'Determine last row with data, Data Input
lastRw1 = Sheets("Data Input").Range("B" & Rows.Count).End(xlUp).Row
'Determine last row with data, TB Input
lastRw2 = Sheets("TB Input").Range("D" & Rows.Count).End(xlUp).Row
'Loop through Import, Column A
For nxtRw = 7 To lastRw2
'Search Data Input Column B for value from Data Input
With Sheets("Data Input").Range("B2:B" & lastRw1)
Set m = .Find(Sheets("Data Input").Range("A" & nxtRw), LookIn:=xlValues, lookat:=xlWhole)
'Search Data Input Date for Date value from Data Input
With Sheets("Data Input").Range("D2")
Set datem = .Find(Sheets("Data Input").Range("A" & nxtRw), LookIn:=xlValues, lookat:=xlWhole)
'Copy if match is found
If Not m & datem Is Nothing Then
Sheets("Data Input").Range("D" & nxtRw & ":O" & nxtRw).Copy _
Destination:=Sheets("TB Input").Range("F" & m.Row)
End If
End With
Next

End Sub

 

[excelforfinance]

Re: copy import data to another worksheet as long as it matches date and account #

Data Input Tab:

TB Input Tab: