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

Starstopper31

New Member
Joined
Jan 19, 2015
Messages
3
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 1.JPG


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


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


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.
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

Oddball2020

Board Regular
Joined
May 6, 2021
Messages
69
Office Version
  1. 365
Platform
  1. Windows
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?

VBA Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,132,980
Messages
5,656,203
Members
418,289
Latest member
Kjohno

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top