How to Paste Values into Every Fifth Excel Row with VBA in WPS Office
Question details
The user needs a VBA macro that copies a consecutive source column and writes each value to every fifth row of a destination column, leaving four rows between entries.
- Product
- WPS Office Spreadsheet
- Device & OS
- not provided
- Scenario
- Data is stored consecutively in a source column, and the user wants to distribute those values into a destination sheet with four blank or protected rows between each pasted value.
- Observed behavior
- A macro should loop through the source values and increase the destination row by five after each write, so the destination column is populated at 5-row intervals.
Make sure your source column is contiguous and the destination worksheet is not protected, or temporarily remove protection from the target cells. Confirm that macros are enabled in WPS Office Spreadsheet so the VBA code can run.
Use VBA to Write Source Values to Every Fifth Destination Row
Use this method when you have a contiguous source column and need a reusable macro to distribute those values at fixed five-row intervals in the destination sheet.
The code clears the destination column first, reads the last used row in the source column, and loops from the first source row. It writes each value to the destination column and increments the destination row by five so four rows remain untouched between entries.
Open the workbook in WPS Office Spreadsheet and press Alt+F11 to open the VBA editor.
In the Project Explorer, right-click VBAProject, choose Insert > Module, and open the new module window.
Copy the provided VBA code into the module. Adjust the source worksheet, source column, destination worksheet, destination column, and starting row as needed.
Place the cursor inside the Sub PasteToEveryFifthRow procedure and press F5, or close the editor, press Alt+F8, select PasteToEveryFifthRow, and click Run.
Check the destination worksheet to confirm that each source value appears in the target column and that four rows are left blank between values.
Automate Paste-Every-Fifth-Row Tasks in WPS Office Spreadsheets
WPS Office can complete this task directly with VBA. Open the built-in VBA editor, run the macro, and let WPS Spreadsheets space the source values into every fifth row while preserving the rows in between.
- 1. Open the workbook: Open the target workbook in WPS Office Spreadsheet.
- 2. Open the VBA editor: Press Alt+F11 to open the VBA editor.
- 3. Insert a module and paste the macro: Right-click the project, choose Insert > Module, and paste the provided VBA code.
- 4. Update the references: Change the sheet names, column letters, and starting row to match your workbook.
- 5. Run and verify the macro: Press F5 to run the macro, then check that values appear every fifth row in the destination column.
Frequently Asked Questions
Can I use a different source column instead of column B?
Yes. Change the source reference in the code, such as Cells(i, "B") to Cells(i, "A") or another column, and update the lastRow calculation to count that same source column.
How do I make the macro start at a different destination row?
Set destinationRow = 5 or any other starting row before the For loop. The macro writes the first source value to that row and then adds 5 for each following value.
Why does the macro leave four rows between entries?
Because the code uses destinationRow = destinationRow + 5 after each write. To use a different interval, change the number 5 to the desired spacing.
What should I do if the destination cells are protected?
Unprotect the destination sheet before running the macro, or add a line to the macro that unprotects the sheet before writing and reprotects it after the loop. Macros cannot write to locked cells while protection is active.
Does this work when the source data has empty cells in the middle?
The loop stops at the last populated row in the source column, so empty rows after that are not processed. Empty cells within the source range are written as blank values unless you add a condition to skip them.
