Hello,
I am trying to put together a complicated (to me) code that will take two worksheets, compare ID's in each and when a match is found move over several cells copy that, go back to the other sheet match and then move over from the matching ID and paste. I found this code and it would work great, except I do not know how to switch the Sheets around. The code below is searching the Country List workbook and sheet (My Master List), finding its match in the 10-21-2022 workbook, off setting and copying the data from the 10-21-2022 then pasting it in Country List. Which is almost what I want it to do, but I need to reverse it and do not see --what I am sure is an obvious answer--how I do that. The 10-21-2022 sheet is going to change every week--that is why it is named by the date. The Country List is a master List. I do not want to search for every item in the master list...I want to search every item in the weekly list find if it has a matching value in the master list then copy the data in the Weekly sheet and paste to Master List. See Below:
Sub findCountry()
Dim FindString As String
Dim Rng As Range
For Each cell In Workbooks("Country List.xlsx").Worksheets("Jeff").Range("D:D")
FindString = cell.Value
'Workbooks("10-21-2022.xlsx").Worksheets("10-21-2022").Range("F:F")
If Trim(FindString) <> "" Then
With Workbooks("10-21-2022.xlsx").Worksheets("10-21-2022").Range("F:F")
Set Rng = .Find(What:=FindString, _
After:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not Rng Is Nothing Then
Application.Goto Rng, True
TheCountry = ActiveCell.Offset(0, 13).Value
cell.Offset(0, 7).Value = TheCountry
End If
End With
End If
Next
End SubObviously, I do not know how this is working other than it is set up to find a match and copy that data from the same worksheet it is searching and changes sheets to paste. I've tried using the specific sheet names in the active cell code but it errored. If anyone could tell me how to modify this for my needs I would really appreciate it.
Thank you
Kathy