VBA & Macro Problems

Copy Excel Worksheet Data with VBA Without Overwriting Headers

Amos GikundaAmos Gikunda Nov 18, 2026 868 views

Question details

Copy a worksheet column with VBA while preserving the destination header row.

Copy Excel Worksheet Data with VBA Without Overwriting Headers
Product
Microsoft Excel
Device & OS
not provided
Scenario
A user wants to copy data from one worksheet to another without replacing the destination column header.
Observed behavior
The destination header remains intact while the source data is copied below it.
Before you start

Check the source and destination worksheet names, the column to copy, and the row containing each header. The example assumes both sheets are in the workbook containing the macro and that the source header is in B1.

Solution 1Recommended

Copy the column starting below the header

Use matching ranges that begin at row 2 to copy values while leaving the destination header in B1 untouched.

This example finds the last populated row in source column B and copies values only; it does not copy formatting or formulas.

1
Open the VBA editor

In Excel, open the workbook and select Developer > Visual Basic. In the VBA editor, select Insert > Module.

2
Add the copy macro

Paste this code into the module, replacing Sheet1 and Sheet2 with your worksheet names: Sub CopyColumnWithoutHeader() Dim sourceSheet As Worksheet Dim destinationSheet As Worksheet Dim lastRow As Long Set sourceSheet = ThisWorkbook.Worksheets("Sheet1") Set destinationSheet = ThisWorkbook.Worksheets("Sheet2") lastRow = sourceSheet.Cells(sourceSheet.Rows.Count, "B").End(xlUp).Row If lastRow < 2 Then Exit Sub destinationSheet.Range("B2").Resize(lastRow - 1, 1).Value = _ sourceSheet.Range("B2:B" & lastRow).Value End Sub

3
Run and check the macro

Save the workbook as an Excel Macro-Enabled Workbook (.xlsm). In Excel, select Developer > Macros, choose CopyColumnWithoutHeader, and select Run. Check that B1 on the destination sheet is unchanged.

Copy the column starting below the header
Start at a different destination row: To place the source data starting at B8 instead, replace destinationSheet.Range("B2") with destinationSheet.Range("B8"). Resize keeps the source and destination ranges the same height.
Free Microsoft Office alternative

Work with Excel spreadsheets in WPS Office

WPS Office is a free, lightweight Office alternative with a familiar interface for opening and editing spreadsheets. Its Excel format compatibility helps you move everyday files between applications, though it does not fix Excel VBA issues and macro support may vary.

Edit worksheet data and preserve headers using familiar spreadsheet controlsOpen and save compatible Microsoft Excel formats, including XLSXUse a free, lightweight suite with a familiar interfaceMove everyday spreadsheet work from Microsoft Office with minimal adjustment
microsoft office alternative - wps office

Frequently Asked Questions

Why does the macro start copying at B2?

B1 contains the header in this example. Starting both ranges at B2 leaves the destination header untouched.

How do I copy the data to a different starting row?

Change destinationSheet.Range("B2") to the desired starting cell, such as destinationSheet.Range("B8"). The Resize call keeps the destination range the same height as the source range.

Does this macro copy formulas and formatting?

No. The .Value assignment copies cell values, including displayed formula results, but not formulas or formatting.

What happens if there is no data below the source header?

If the last populated row in source column B is above row 2, the macro exits without copying anything.

Why do the source and destination ranges need the same size?

A direct .Value assignment works reliably when the ranges have matching dimensions. The example uses Resize(lastRow - 1, 1) to match the number of source rows.