VBA & Macro Problems

Excel VBA Macro to Copy Each Row to a New Worksheet

Maira Mehtab Sep 20, 2026 869 views

Question details

The user needs an automated way to copy each row of data from a main sample sheet into separate, newly created worksheets, retaining the header row on each new sheet.

Product: Excel
Device & OS: not provided
Scenario: Organizing large datasets by splitting row-specific data into individual worksheets automatically.
Observed behavior: Currently, row-specific data is combined on a single sheet, and the user wants to automate the creation and population of individual worksheets for each data row along with its header.

Before you start

Ensure that your workbook is saved as an Excel Macro-Enabled Workbook (.xlsm) and that you have enabled Developer tools in the ribbon to access the VBA editor.

Solution 1Recommended

Use a VBA Macro to Loop and Copy Rows

This solution involves creating a VBA macro that loops through each row on your sample sheet, generates a new worksheet for each row, and copies both the header and the specific row data.

By utilizing a For loop and Excel's built-in Worksheet objects, you can automate the process of creating multiple sheets and copying data dynamically. This saves significant time compared to manually copying and pasting row data.

Open the Visual Basic Editor

In your open Excel workbook, navigate to the Developer tab on the top ribbon and click on 'Visual Basic'. Alternatively, you can press Alt + F11 on your keyboard.

Insert a New Module

In the Visual Basic Editor, go to the top menu bar, click on 'Insert', and select 'Module'. This will open a blank code window where you can write or paste your macro.

Paste the Macro Code

Copy and paste the following VBA script into the module window: Sub CopyRowsToSheets() Dim wsSample As Worksheet, wsNew As Worksheet Dim lastRow As Long, i As Long Set wsSample = ThisWorkbook.Sheets("Sample Sheet") lastRow = wsSample.Cells(wsSample.Rows.Count, "A").End(xlUp).Row For i = 1 To lastRow Set wsNew = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)) wsNew.Name = "Sheet" & i wsSample.Rows(1).Copy Destination:=wsNew.Rows(1) wsSample.Rows(i).Copy Destination:=wsNew.Rows(2) Next i End Sub

Adjust Worksheet Names

Ensure that the name inside `ThisWorkbook.Sheets("Sample Sheet")` perfectly matches the tab name of your actual data sheet. If your data is on "Sheet1", change "Sample Sheet" to "Sheet1".

Run the Macro

Close the Visual Basic Editor to return to Excel. Press Alt + F8 to open the Macro dialog box, select 'CopyRowsToSheets' from the list, and click 'Run'. The macro will automatically generate the new sheets and copy the rows.

Save as Macro-Enabled Workbook: Always remember to use 'Save As' and select 'Excel Macro-Enabled Workbook (*.xlsm)' to keep your VBA code functional for future use.

Advanced Macro Support

Automate Row Splitting with WPS Spreadsheet

WPS Office fully supports VBA macros, allowing you to run your scripts seamlessly. Use WPS Spreadsheet to quickly copy rows to new sheets with the same macro capabilities as Microsoft Excel.

1. Open WPS Spreadsheet: Launch your workbook containing the row data in WPS Spreadsheet.
2. Access Developer Tools: Navigate to the Developer tab on the ribbon and click on the 'Visual Basic Editor' icon.
3. Insert the VBA Code: Go to Insert > Module in the editor and paste the provided VBA script.
4. Run the Automation: Click the Run icon or press F5 to execute the macro and automatically split your rows into separate sheets.

Free and lightweight office suiteHigh compatibility with Microsoft Excel formats (.xlsm, .xlsx)Full support for VBA and macro automationFamiliar interface for seamless migration

microsoft office alternative - wps office

Frequently Asked Questions

How do I change the macro to name the new worksheets based on column values?

You can modify the `wsNew.Name` line in the VBA code to reference a specific cell. For example, replace `wsNew.Name = "Sheet" & i` with `wsNew.Name = wsSample.Cells(i, 1).Value` to name the sheets based on the values in column A of that row.

Why am I getting a 'Subscript out of range' error when running the macro?

This usually happens if the worksheet name referenced in the script does not exactly match your actual sheet tab name. Ensure the text inside `ThisWorkbook.Sheets("Sample Sheet")` matches the spelling and spacing of your current worksheet tab.

Will this macro work if I have blank rows in my data?

The macro calculates the last used row in column A using `End(xlUp)`. It will process all rows up to that point. If there is a blank row in the middle of your data, it will create a blank sheet for that row. It's recommended to remove entirely blank rows before running the macro.

How can I start copying data from row 2 instead of row 1 to avoid duplicating the header?

In the code's For loop, change `For i = 1 To lastRow` to `For i = 2 To lastRow`. This skips processing row 1 (the header) as a data row, preventing it from being copied twice onto the first newly generated sheet.

Excel & Data Problems