VBA & Macro Problems

How to Use a VBA Macro to Copy Values to Every 29th Row in Excel

Huma Ashraf Ch Oct 15, 2026 870 views

Question details

The user needs a VBA macro to copy data from columns M:O in a 'Scores' worksheet to columns L:N in a 'Categories' worksheet, placing each new entry 29 rows below the previous one and stopping at the first empty row.

How to Use a VBA Macro to Copy Values to Every 29th Row in Excel

Product: Excel
Device & OS: not provided
Scenario: Automating the transfer of data between worksheets where the destination format requires a specific, wide row interval (every 29th row).
Observed behavior: When executing the macro without properly qualified worksheet references, the user encounters Run-time error 1004 (Application-defined or object-defined error) and Run-time error 91 (Object variable or With block variable not set).

Before you start

Before running the macro, ensure that both your source worksheet ('Scores') and destination worksheet ('Categories') exist and are named exactly as they appear in the VBA code.

Solution 1Recommended

Use a Do While Loop with Fully Qualified Worksheet References

By explicitly specifying the worksheet for every Range or Cells reference within a Do While loop, you can bypass common object reference errors and dynamically space your copied data.

Run-time errors 91 and 1004 frequently occur when VBA does not know which worksheet to apply a Cells or Range property to. Qualifying the worksheets for both the source ('Scores') and destination ('Categories') resolves this.

A loop evaluates the length of the string in the source column; as long as it isn't empty, it copies the 3-column range, increments the source row by 1, and jumps the destination row by 29.

Open the VBA Editor

Press Alt + F11 on your keyboard to open the Microsoft Visual Basic for Applications (VBA) editor.

Insert a New Module

In the VBA editor, click 'Insert' from the top menu and select 'Module' to create a new blank script window.

Paste the Spaced Copy Macro

Copy and paste the following code: Sub CopyToEvery29thRow() Dim r As Long, destRow As Long r = 2 destRow = 2 Do While Len(Worksheets("Scores").Cells(r, "M").Value) > 0 Worksheets("Categories").Cells(destRow, "L").Resize(1, 3).Value = Worksheets("Scores").Cells(r, "M").Resize(1, 3).Value r = r + 1 destRow = destRow + 29 Loop End Sub

Run the Macro

Place your cursor anywhere inside the pasted code and press F5, or click the green 'Run' triangle in the toolbar. Check your 'Categories' sheet to verify the values are pasted every 29 rows.

Use a Do While Loop with Fully Qualified Worksheet References

Error Prevention: Qualifying every object reference (e.g., Worksheets("Scores").Cells(...)) directly prevents both Run-time error 91 and 1004.

Advanced Macros Support

Automate Data Transfers Easily with WPS Spreadsheet

WPS Office provides robust compatibility with VBA macros, allowing you to run, edit, and debug scripts—like copying data to specific row intervals—just as you would in Microsoft Excel.

1. Open your Workbook in WPS Spreadsheet: Launch WPS Office and open your .xlsm file containing the 'Scores' and 'Categories' worksheets.
2. Access the Developer Tools: Navigate to the 'Developer' tab on the top ribbon. If you do not see it, you may need to enable VBA support depending on your WPS version.
3. Run the Copy Macro: Click on 'Macros', select your 'CopyToEvery29thRow' macro from the list, and click 'Run' to seamlessly distribute your data.

Fully compatible with Microsoft Excel .xlsm and .xlsb macro formatsIntegrated VBA editor to comfortably write and execute your own custom loopsLightweight architecture ensures large data transfers complete swiftlyFree to download with a familiar UI, making migration effortless

microsoft office alternative - wps office

Frequently Asked Questions

Why do I get Run-time error 1004 when running my Excel VBA macro?

Run-time error 1004 typically occurs if the specified range is invalid, or if the macro attempts to act on a worksheet that isn't fully qualified. Ensure you explicitly add `Worksheets("SheetName")` before calling `.Cells` or `.Range`.

What causes Run-time error 91 (Object variable not set) in this scenario?

This error happens when an object reference evaluates to nothing. In VBA copying tasks, it often implies the worksheet name referenced in the code is misspelled, missing, or the active workbook has changed. Fully qualifying references resolves this.

How do I change the macro to copy every 15th row instead of the 29th?

You can easily adjust the row interval by modifying the incrementation line in the VBA code. Locate the line that says `destRow = destRow + 29` and change the number `29` to `15`.

Can I use the `.Resize` method to copy more columns?

Yes. The `.Resize(1, 3)` method tells VBA to expand the selection by 1 row and 3 columns. If you need to copy 5 columns (for example, columns M through Q), you would simply change it to `.Resize(1, 5)` on both sides of the equation.

Excel & Data Problems