Excel VBA Macro: Pull Specific Columns from Another Workbook (Based on Header Values) Dynamically

Excel VBA Macro: Pull Specific Columns from Another Workbook (Based on Header Values) Dynamically. In this video, we create a macro that automatically populates the current workbook with data from specific columns in another workbook from a specified location. We choose the columns we wish to pull by typing the corresponding headers in our workbook, and using a Do While Loop nested in a For Loop to match off the headers and copy and paste the desired columns.

Code (YouTube doesn't allow brackets; so LT and GT are used for less than and greater than, respectively):

Sub pull_columns()

Dim head_count As Integer
Dim row_count As Integer
Dim col_count As Integer
Dim i As Integer
Dim j As Integer
Dim ws As Worksheet

Application.ScreenUpdating = False

Set ws = ThisWorkbook.Sheets("Sheet1")

'count headers in this workbook
head_count = WorksheetFunction.CountA(Range("A1", Range("A1").End(xlToRight)))

'open other workbook and count rows and columns
Workbooks.Open Filename:="C:\Users\greggowaffles\Documents\Youtube Videos\Test\Some Files\Cities.xlsx"
ActiveWorkbook.Sheets(1).Activate

row_count = WorksheetFunction.CountA(Range("A1", Range("A1").End(xlDown)))
col_count = WorksheetFunction.CountA(Range("A1", Range("A1").End(xlToRight)))


For i = 1 To head_count

j = 1

Do While j LT = col_count

If ws.Cells(1, i) = ActiveSheet.Cells(1, j).Text Then

ActiveSheet.Range(Cells(1, j), Cells(row_count, j)).Copy
ws.Cells(1, i).PasteSpecial xlPasteValues
Application.CutCopyMode = False
j = col_count

End If

j = j + 1

Loop

Next i

ActiveWorkbook.Close savechanges:=False

ws.Cells(1, 1).Select

Application.ScreenUpdating = True

End Sub

#ExcelVBA #ExcelMacro