VBA & Macro Problems

Copy Excel Values to Another Sheet with Three Blank Rows Using VBA

Muhammad TalhaMuhammad Talha Nov 18, 2026 869 views

Question details

Copy worksheet values to another sheet with three blank rows between entries using a VBA macro.

Copy Excel Values to Another Sheet with Three Blank Rows Using VBA
Product
Microsoft Excel
Device & OS
not provided
Scenario
Values in one worksheet need to be placed four rows apart in another worksheet.
Observed behavior
Each copied value should be followed by three blank rows before the next value.
Before you start

Open the workbook in a desktop version of Excel that supports VBA. Check the source and destination sheet names and make sure the destination cells can be cleared before running the macro.

Solution 1Recommended

Use a VBA macro to copy values four rows apart

Use this method to copy values from column A of one sheet to column A of another, leaving three blank rows between them.

The example reads every row from A1 through the last used cell in column A, including any blank source rows. It copies values only, not formatting or formulas.

The macro clears the destination range it uses before copying. Change the sheet names, columns, or starting rows in the code if your workbook uses different locations.

1
Open the VBA editor

In Excel, open the workbook, press Alt+F11, and choose Insert > Module.

2
Add the macro

Paste this code into the module. Replace Source and Destination with the names of your worksheets: Sub CopyValuesWithBlankRows() Dim src As Worksheet, dst As Worksheet Dim lastRow As Long, srcRow As Long, dstRow As Long Set src = ThisWorkbook.Worksheets("Source") Set dst = ThisWorkbook.Worksheets("Destination") If Application.WorksheetFunction.CountA(src.Columns("A")) = 0 Then Exit Sub lastRow = src.Cells(src.Rows.Count, "A").End(xlUp).Row dst.Range("A1:A" & (1 + (lastRow - 1) * 4)).ClearContents dstRow = 1 For srcRow = 1 To lastRow dst.Cells(dstRow, "A").Value = src.Cells(srcRow, "A").Value dstRow = dstRow + 4 Next srcRow End Sub

3
Save as a macro-enabled workbook

Choose File > Save As and select Excel Macro-Enabled Workbook (*.xlsm). Enable macros only if you trust the workbook and its code.

4
Run the macro

Return to Excel, press Alt+F8, select CopyValuesWithBlankRows, and click Run. Check the Destination sheet for values in A1, A5, A9, and subsequent fourth rows.

Use a VBA macro to copy values four rows apart
Destination cells are cleared: The macro removes existing contents in the destination column from A1 through the last output row. It does not clear formatting or cells below that range.
Free Microsoft Office alternative

Work with spreadsheets in WPS Office

WPS Office is a free, lightweight alternative with a familiar interface for opening and editing spreadsheet files. It supports common Excel formats, making it easy to move everyday spreadsheet work between applications. VBA macro availability and compatibility depend on the WPS edition and environment.

Copy and arrange worksheet values in WPS SpreadsheetsOpen and edit Microsoft Excel XLSX and XLS filesUse a familiar spreadsheet interface for everyday workMove common spreadsheet files between WPS Office and Microsoft Office
microsoft office alternative - wps office

Frequently Asked Questions

Why does the macro increase the destination row by four?

After writing a value, the line dstRow = dstRow + 4 moves to the next output row, leaving the three rows in between blank.

How do I use different worksheet names?

In the VBA editor, replace "Source" and "Destination" in the Worksheets statements with the exact names shown on your workbook's sheet tabs.

Will the macro copy formulas and formatting?

No. The .Value assignment copies the displayed result of a formula as a value; it does not copy the formula or cell formatting.

What happens if the source column contains blank cells?

The macro processes each row through the last used cell in column A, including blank rows. A blank source row therefore occupies an output position and can create a larger gap between nonblank values.

Why should I save the workbook as XLSM?

The Excel Macro-Enabled Workbook (*.xlsm) format preserves VBA code. Use File > Save As to select it, and enable macros only for trusted files.