No announcement yet.

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

  • Filter
  • Time
  • Show
Clear All
new posts

  • 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!
    Attached Files

  • #2
    I assume that the country name in cell C3 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?
    You can say "THANK YOU" for help received by clicking "Like" in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.


    • #3
      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


      • #4
        hy HestiA copy this code to WB2 ,open your both workbooks
        Sub test()
        Dim x, xH, c As Range, fn
        With Workbooks("Workbook 1.xlsx").Sheets("Sheet1")
          x = .UsedRange.Offset(1).Value
          xH = .UsedRange.Rows(1).Value
        End With
        With ThisWorkbook.Sheets("Sheet1").UsedRange
           For Each c In .Rows(1).Cells
               fn = Application.Match("*" & c.Value & "*", xH, 0)
               If Not IsError(fn) Then
                  ActiveCell.Resize(UBound(x, 1)) = Application.Index(x, 0, fn)
               End If
           Next c
        End With
        End Sub


        • #5
          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
          Last edited by HestiA; November 12th, 2018, 16:57.


          • #6

            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


            • #7
              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


              • #8

                Hi Graha,

                Thanks for sharing! Managed to fit in the codes into my arrays now

                ~ Hestia