Match cells from another workbook & then copy

[rhysm144]

I am dumbfounded making a macro to do the following (unsure if even possible):

If name (I.e. Smith,John) in AB19, matches AB19 in Workbook “STK” then copy cells A1:T6 into A1. It would need to match AB19 to AB19 across a workbook of over 100 sheets though i.e. if no match in the first sheet, try the second and so forth.

After matching and copying, it would then move onto the next sheet in the active workbook and do the same thing for AB19 in the next sheet and continue through all sheets until the end.

Thanks

[onlyadrafter]

Hello,

is this close to your requirements??


Sub GET_MATCH_AB19() Application.ScreenUpdating = False
MY_SOURCE = ActiveWorkbook.Name
For MY_SOURCE_SHEET = 1 To Workbooks(MY_SOURCE).Sheets.Count
MY_SOURCE_NAME = Sheets(MY_SOURCE_SHEET).Range("AB19").Value
Workbooks("STK").Activate
For MY_LIST_SHEET = 1 To Workbooks("STK").Sheets.Count
If Sheets(MY_LIST_SHEET).Range("AB19").Value = MY_SOURCE_NAME And Not (IsEmpty(MY_SOURCE_NAME)) Then
Sheets(MY_LIST_SHEET).Range("A1:T6").Copy
Workbooks(MY_SOURCE).Activate
Sheets(MY_SOURCE_SHEET).Range("A1").PasteSpecial (xlPasteAll)
MY_SOURCE_NAME = ""
GoTo CONT
End If
Next MY_LIST_SHEET
CONT:
Next MY_SOURCE_SHEET
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

you need to be in 'other' spreadsheet to start with.

Have assumed that once it finds a match, it doesn;t need to like in any of the other sheets.

[rhysm144]

Perfect, thanks