Copy Excel Worksheet Data with VBA Without Overwriting Headers
Question details
Copy a worksheet column with VBA while preserving the destination header row.

- 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.
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.
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.
In Excel, open the workbook and select Developer > Visual Basic. In the VBA editor, select Insert > Module.
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
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.

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.

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.




