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 (RU-vid 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\RU-vid 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
1 янв 2022
