Announcement

Collapse
No announcement yet.

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

Collapse
X
  • 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

  • HestiA
    replied
    Hi Graha,

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

    ~ Hestia

    Leave a comment:


  • graha_karya
    replied
    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
    c(2).select
    Similar with c.offset(1)
    Maybe you can imagine c(2)
    Code:
    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

    Leave a comment:


  • HestiA
    replied

    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:

    Code:
      c(2).Select
    Is there a way where i can solve this issue?

    Thanks!

    ~ Hestia

    Leave a comment:


  • HestiA
    replied
    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.

    Leave a comment:


  • graha_karya
    replied
    PERHAB LIKE THIS
    hy HestiA copy this code to WB2 ,open your both workbooks
    Code:
    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
       .Offset(1).ClearContents
       For Each c In .Rows(1).Cells
           fn = Application.Match("*" & c.Value & "*", xH, 0)
           If Not IsError(fn) Then
              c(2).Select
              ActiveCell.Resize(UBound(x, 1)) = Application.Index(x, 0, fn)
           End If
       Next c
    End With
    End Sub

    Leave a comment:


  • HestiA
    replied
    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

    Leave a comment:


  • Mumps
    replied
    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?

    Leave a comment:

Working...
X