Excel VBA Macro: Pull Specific Columns from One Sheet to Another (Dynamically). In this video, we create a macro that automatically populates a worksheet with data from specific columns on another sheet from the same workbook. 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 (KZfaq doesn't allow brackets; so LT and GT are used for less than and greater than, respectively):
Sub pull_columns_over()
Dim head_count As Integer
Dim row_count As Integer
Dim col_count As Integer
Dim i As Integer
Dim j As Integer
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Set ws1 = ThisWorkbook.Sheets("Raw Data")
Set ws2 = ThisWorkbook.Sheets("Sheet2")
ws2.Activate
head_count = WorksheetFunction.CountA(Range("A1", Range("A1").End(xlToRight)))
ws1.Activate
col_count = WorksheetFunction.CountA(Range("A1", Range("A1").End(xlToRight)))
row_count = WorksheetFunction.CountA(Range("A1", Range("A1").End(xlDown)))
For i = 1 To head_count
j = 1
Do While j LT= col_count
If ws2.Cells(1, i) = ws1.Cells(1, j).Text Then
ws1.Range(Cells(1, j), Cells(row_count, j)).Copy
ws2.Cells(1, i).PasteSpecial xlPasteValues
Application.CutCopyMode = False
j = col_count
End If
j = j + 1
Loop
Next i
With ws2
.Activate
.Cells(1, 1).Select
End With
End Sub
#ExcelVBA #ExcelMacro