VBA & Macro Problems

Create Excel Sheets from a Master List in Groups of 81 Using VBA

Ayan Masood Oct 22, 2026 868 views

Question details

The user needs an automated method to extract data from a single master list and copy it into multiple template sheets in batches of 81 records per sheet.

Create Excel Sheets from a Master List in Groups of 81 Using VBA

Product: Excel
Device & OS: not provided
Scenario: Generating multiple standardized template sheets (such as 9x9 box layouts for lab samples) from a large master data list, with dynamic naming and empty slot handling.
Observed behavior: The data needs to be split into new sheets named sequentially by box number, and any remaining empty positions in an 81-slot template must be automatically filled with the text 'NO TUBE'.

Before you start

Ensure your workbook contains two worksheets named exactly 'Master' (containing your source data) and 'Template' (the layout to be copied), and verify that your file is saved as a Macro-Enabled Workbook (.xlsm).

Solution 1Recommended

Automate Sheet Creation using a VBA Macro

A VBA macro utilizing a Step 81 loop can efficiently copy the template, transfer 81 rows of data per iteration, and intelligently handle missing entries.

This VBA solution loops through the 'Master' sheet, advancing by 81 rows at a time. During each step, it duplicates the 'Template' sheet, renames it sequentially based on a box count variable, and populates the required columns from the master data. If a batch contains fewer than 81 records, the script uses an IF statement to fill the remaining rows with 'NO TUBE'.

Open the VBA Editor

Press 'ALT + F11' in your Excel workbook to open the Microsoft Visual Basic for Applications editor.

Insert a New Module

Click 'Insert' in the top menu bar, then select 'Module' to create a blank scripting window.

Paste the VBA Code

Paste your custom script into the module. Ensure your loop reads 'For i = 2 To lastRow Step 81' and your inner loop uses 'For j = 0 To 80' to manage the 81-row batches.

Configure Missing Data Handling

Within the inner loop, use an 'If i + j <= lastRow' condition to map data columns. In the 'Else' block, instruct the code to write 'NO TUBE' for any unused positions.

Run the Macro

Press 'F5' or click the green 'Run' button on the toolbar to execute the macro. The new sheets will be instantly generated and populated.

Automate Sheet Creation using a VBA Macro

Macro Execution: The newly generated sheets will be saved within the same workbook, automatically named using your predefined box count convention.

Advanced Spreadsheet Automation

Automate Your Workflows with WPS Spreadsheet

WPS Office provides robust built-in support for VBA macros, allowing you to seamlessly execute custom scripts to manipulate bulk data, such as splitting master lists into multiple template sheets.

1. Open your macro file in WPS: Launch WPS Spreadsheet and open your existing .xlsm file containing the master list.
2. Access the Developer Tools: Navigate to the 'Developer' tab on the top ribbon and click on 'Macros' or 'Visual Basic'.
3. Input the VBA script: Insert a new module and paste your Step-81 loop script into the editor.
4. Execute the automation: Run the script directly within WPS Spreadsheet to instantly generate your populated template sheets.

Full compatibility with Microsoft Excel (.xlsx and .xlsm) file formatsBuilt-in VBA/Macro support for automating repetitive data tasksLightweight application that runs smoothly and efficiently on most devicesFamiliar user interface makes transitioning and writing code effortless

microsoft office alternative - wps office

Frequently Asked Questions

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

This error occurs if the macro cannot find sheets with the exact specified names. Double-check the sheet tabs at the bottom of your workbook to ensure they are named exactly 'Master' and 'Template' without any hidden spaces.

How can I change the macro to group by 50 rows instead of 81?

In your VBA code, change the outer loop command to 'Step 50'. Additionally, you must update the inner loop to 'For j = 0 To 49' to match the new batch size minus one.

How do I save the file so my macro is preserved for future use?

Standard Excel workbooks (.xlsx) cannot store macros. You must go to File > Save As and select 'Excel Macro-Enabled Workbook (*.xlsm)' from the file format dropdown menu.

Excel & Data Problems