Copy Excel Values to Another Sheet with Three Blank Rows Using VBA
Question details
Copy worksheet values to another sheet with three blank rows between entries using a VBA macro.

- 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.
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.
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.
In Excel, open the workbook, press Alt+F11, and choose Insert > Module.
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
Choose File > Save As and select Excel Macro-Enabled Workbook (*.xlsm). Enable macros only if you trust the workbook and its code.
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.

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.

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.




