VBA Copy/Paste based on adjacent cell value in multiple workbooks

[Starstopper31]

Hello,

I am fairly new to VBA and have tried searching for an example of this with limited luck so far. I'm not sure if my question is too specific or I'm wording it wrong. I am trying to copy and paste data from one workbook to another using VBA. In workbook #1, I have data in both columns "A" and "B". In workbook #2, I have the same values in column "A" (in a different order), and column "B" is blank.

I am trying to find a way to copy the data in the "B" column from Workbook #1 and paste it into the empty "B" column in Workbook #2. The trick is getting the data to show up in the correct row in Workbook #2 so that the values in column "A" still match with "B" in Workbook #2. Both ranges are dynamic, so I can't just paste from one designated cell to another because the rows change.

Please see example below:

Workbook #1

Workbook #2 - (How it looks when the workbook opens)

Workbook #2 - (What I am wanting the code to do)

My actual file is more complicated than this, but I feel like I can figure the rest out if I can find an example to use as a baseline. Any help would be appreciated. Thanks.

 

[Oddball2020]

Hello,

I am fairly new to VBA and have tried searching for an example of this with limited luck so far. I'm not sure if my question is too specific or I'm wording it wrong. I am trying to copy and paste data from one workbook to another using VBA. In workbook #1, I have data in both columns "A" and "B". In workbook #2, I have the same values in column "A" (in a different order), and column "B" is blank.

I am trying to find a way to copy the data in the "B" column from Workbook #1 and paste it into the empty "B" column in Workbook #2. The trick is getting the data to show up in the correct row in Workbook #2 so that the values in column "A" still match with "B" in Workbook #2. Both ranges are dynamic, so I can't just paste from one designated cell to another because the rows change.

Please see example below:

Workbook #1
View attachment 36600

Workbook #2 - (How it looks when the workbook opens)
View attachment 36601

Workbook #2 - (What I am wanting the code to do)
View attachment 36602

My actual file is more complicated than this, but I feel like I can figure the rest out if I can find an example to use as a baseline. Any help would be appreciated. Thanks.

This should work... it's dynamic. It's not as clean as it could be or others may prefer, but it'll be ok. Do you know how to create a macro and copy this code into it?

Dim WKB1 As Workbook
    Set WKB1 = ThisWorkbook  'This is your Workbook 1, that is running the macro
    
    Dim myPath As String
    myPath = Application.ThisWorkbook.Path    'this determines the folder location of Workbook 1; assumes Workbook 2 is in the same folder
    
    Sheets("Workbook1 Sheet Name").Activate
    
    Dim lastRow As Long
    lastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row   'determines the last row in your Col A
    
    'Open Workbook 2 file
    Application.DisplayAlerts = False
    Workbooks.Open (myPath & "\WorkBook2.xlsx")  'change the name to your file for Workbook 2
    Application.DisplayAlerts = True
    
    Dim WKB2 As Workbook
    Set WKB2 = ActiveWorkbook  'short hand reference to refer to Workbook 2 later despite the actual file name
    
    Sheets("Workbook2 Sheet Name").Activate   'gets on the right sheet you want to copy to
    
    Dim i As Long
    Dim FINDA As String
    Dim VALA As Long
           
    'Loop down your Workbook 1 list, and find the match on workbook 2
    For i = 1 To lastRow
    
    FINDA = WKB1.Sheets("Workbook1 Sheet Name").Cells(i, 1).Value   'the name in Col A
    VALA = WKB1.Sheets("Workbook1 Sheet Name").Cells(i, 2).Value   'the value on Col B
    
    WKB2.Activate
    
    MATA = WorksheetFunction.Match(FINDA, Columns(1), 0)   'finds the name match down Col A in Workbook 2
    
    Cells(MATA, 2).Value = VALA   'this places the value into Col B to the right of the matched word in A
    
    WKB1.Activate  ' bounces back to workbook 1 to do it all again
    
    Next i
    
    MsgBox ("All done!")   'say whatever you want in the quotes, but it's nice for the macro to let you know it's complete