OzGrid

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

Requirement:

The user is using VBA macros AND 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)". The user 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".

https://www.ozgrid.com/forum/forum/help-forums/excel-vba-macros/1211057-copy-paste-column-in-wkbk-1-if-its-cell-has-text-which-matches-with-a-cell-of-wbk-2

Solution:

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

Obtained from the OzGrid Help Forum.

Solution provided by graha_karya.

See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets

See also:

Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.

Gallery