Copy & paste column in wkbk 1 if its cell has text which matches with a cell of wbk 2

  • Hi all

    I am still new to Excel VBA as a whole, and would like to seek help from experts regarding a new issue I have. Using vba macros, I would like to copy a column of data from workbook 1 into workbook 2, based on the data it has on row 3, then loop through for the rest of the columns.

    For example, the cell on row 3 of column C in 'workbook 1' has text called "U.S. Exports to Argentina of Crude Oil (Thousand Barrels)". I would like to copy the whole column and paste it in a column of 'workbook 2' whose first row has "Argentina", since both cells have "Argentina".

    Is it alright if anyone could help me with this?

    Thank you for your help!

  • I assume that the country name in cell C3 [SIZE=13px]in 'workbook 1' can change. Therefore, you would probably want to match the country name in C3, whatever it might be, to the country name in row 1 of 'workbook2'. Is this correct?[/SIZE]

    You can say "THANK YOU" for help received by clicking the "Like" icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Hi Mumps,

    Yes your right! Thats why i am not sure on how to build a macro to detect if the top cell of the specific country column has the country data i want. Worksheet 1 is updated monthly, so this column of "Argentina" data might have a top cell name change or be completely removed.

    I had done macro for simple copy and paste, or using arrays to store values and calling them out before, but im not sure how to add if else statements for this issue.

    ~ Hestia

    hy HestiA copy this code to WB2 ,open your both workbooks

  • Hi Graha,

    Thanks! This set of codes work perfectly! How am I able to check if there are extra countries that are not present in workbook 1?

    ~ Hestia

  • I had added more columns of data in workbook 1 to be copied into workbook 2. I believed this caused me to receive a "select method of range class failed" error for this line of code:


    Is there a way where i can solve this issue?


    ~ Hestia

  • HestiA i am using
    Usedrange in workbook(1) and workbook(2)
    it had dinamic range if you add more column or data should be can to define range
    Similar with c.offset(1)
    Maybe you can imagine c(2)

    dim r as range
              r =range("a1:b10")
             msgbox r(1,1).address & " similar " & [a1].address
             msgbox r(2,1) & "similar " & [a1].offset(1).address 
    If you using r(row,col) = similar using array

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!