Excel VBA Macro: Pull Specific Columns from One Sheet to Another (Dynamically)

Introduction

This tutorial guides you through creating an Excel VBA macro that dynamically pulls specific columns from one worksheet to another within the same workbook. This is particularly useful for organizing data efficiently by selecting only the necessary columns based on their headers. By following these steps, you'll enhance your Excel skills and automate your data management tasks.

Step 1: Set Up Your Worksheets

Before you begin coding, ensure that your workbook contains the necessary sheets.

• Create two sheets in your workbook:
  • "Raw Data": This sheet should contain all your data with headers in the first row.
  • "Sheet2": This is where you will pull the specified columns based on the headers you enter.

Step 2: Open the VBA Editor

To write your macro, you need to access the VBA editor.

• Press ALT + F11 to open the VBA editor.
• In the editor, right-click on any of the items in the Project Explorer, select Insert, and then choose Module. This will create a new module where you can write your code.

Step 3: Write the Macro Code

Now, you will write the macro code that performs the column pulling.

• Copy and paste the following code into the module:

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 <= 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

Step 4: Save Your Macro

After writing the code, save your work.

• Click the File menu, select Save, and ensure your workbook is saved as a macro-enabled file format (e.g., .xlsm).

Step 5: Run Your Macro

Now that your macro is ready, it's time to run it.

• Return to Excel by closing the VBA editor.
• Press ALT + F8 to open the Macro dialog box.
• Select pull_columns_over and click Run.

Step 6: Verify the Results

Check the "Sheet2" to ensure that the specified columns from "Raw Data" have been copied correctly.

• Make sure the headers you typed in "Sheet2" match those in "Raw Data".
• Verify that only the desired data appears under the correct headers.

Conclusion

You have successfully created a VBA macro to pull specific columns from one sheet to another dynamically. This automation saves time and reduces errors in data management. To further enhance your skills, experiment with modifying the code to include additional features, such as error handling or user prompts for dynamic input. Happy coding!