How to Copy Columns to Another Sheet by Matching Headings Using VBA

Before deploying any code, it is crucial to understand the workbook architecture required for a successful VBA Macro, Copy columns from one sheet into another sheet with heading match criteria. Users operating within the ecosystem of Microsoft 365 and Office | Excel | For home | Windows must ensure their source and destination environments are properly formatted

Understanding the Setup for: VBA Macro, Copy columns from one sheet into another sheet with heading match criteria

Before changing anything else, begin with the interface and account named in this action: Open your workbook and press ALT + F11 to launch the Visual Basic for Applications (VBA) Editor

Execution: VBA Macro, Copy columns from one sheet into another sheet with heading match criteria

This Copy Columns to Another Sheet by Matching Headings Using VBA procedure uses the controls available in Standard toolbar; complete each action before moving to the next.

1. Open your workbook and press ALT + F11 to launch the Visual Basic for Applications (VBA) Editor
2. In the top menu path, navigate to Insert > Module to create a new blank script window
3. Paste the exact verified code below into the module window
4. Press F5 on your keyboard or click the Run Sub/UserForm button in the top toolbar to execute the macro
5. Verification: Navigate to the "Data for Consultants" worksheet. Verify that data populates starting from row 2 downward, exactly matching the headers in row 1

Sub CopyModified()

        Dim ws As Worksheet

        Dim wt As Worksheet

        Dim cs As Long

        Dim ct As Range

        Dim ms As Long

        Application.ScreenUpdating = False

        Set ws = Worksheets("Data")

        ms = ws.Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

        Set wt = Worksheets("Data for Consultants")

        For cs = 1 To 57

            Set ct = wt.Range("1:1").Find(What:=ws.Cells(1, cs).Value, LookAt:=xlWhole, MatchCase:=False)

            If Not ct Is Nothing Then

                ws.Range(ws.Cells(2, cs), ws.Cells(ms, cs)).Copy Destination:=ct.Offset(1)

            End If

        Next cs

        Application.CutCopyMode = False

        Application.ScreenUpdating = True

    End Sub

How to Verify the VBA Editor Result

Run the final check exactly as described: Verification: Navigate to the "Data for Consultants" worksheet. Verify that data populates starting from row 2 downward, exactly matching the headers in row 1 If it fails, return to Standard toolbar and confirm that Run Sub/UserForm was applied to the intended item.

WPS Office: A Free Microsoft Office Alternative for Copy Columns to Another Sheet by Matching Headings

Use Word, Excel, and PPT for FREE

Free Download

For local work related to Copy Columns to Another Sheet by Matching Headings Using VBA, WPS Office is a free Microsoft Office-compatible alternative. It does not reproduce every proprietary VBA Editor service or administrator control, so use the Microsoft steps above when the task depends on that specific platform.

While you complete “Copy Columns to Another Sheet by Matching Headings Using VBA” in Microsoft’s interface, use WPS Spreadsheets for XLSX and CSV files, formulas, charts, and routine worksheet editing; WPS AI can also explain formulas and summarize worksheet content; VBA compatibility should be tested separately. Because the desktop interface follows familiar document, spreadsheet, presentation, and PDF conventions, most users can move common local work without rebuilding their workflow.

VBA Editor FAQs About Copy Columns to Another Sheet by Matching Headings Using VBA

Why is the destination sheet empty after the VBA macro runs?

The macro only copies a source column when its row 1 header exactly matches a header in the destination sheet. Check both header rows for spelling differences, trailing spaces, and line breaks. Because the code uses LookAt:=xlWhole, even one extra space prevents a match.

How do I make the macro scan more than 57 source columns?

Change the upper limit in For cs = 1 To 57 to the number of source columns you need. For example, column BO is column 67, so use For cs = 1 To 67. Keep the loop aligned with the actual last source column so unnecessary blank columns are not scanned.

Will the macro remove old rows from the destination sheet?

No. It overwrites matched destination columns from row 2 downward but does not clear leftover rows from an earlier, longer data set. Clear the previous data area below the headers before running the macro if the new source may contain fewer records.

Are header matches in this VBA code case-sensitive?

No. MatchCase:=False allows headers such as CLIENT ID and Client Id to match. The text still needs to be otherwise identical, including punctuation and hidden spaces.