Consulting

Results 1 to 3 of 3

Thread: Match cells from another workbook & then copy

  1. #1
    VBAX Regular
    Joined
    Oct 2016
    Posts
    20
    Location

    Match cells from another workbook & then copy

    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

  2. #2
    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.
    ---------------
    Hope this helps
    ---------------

    Have been away for a very long time,
    but am popping back again (now and then).

  3. #3
    VBAX Regular
    Joined
    Oct 2016
    Posts
    20
    Location
    Perfect, thanks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •