How to Repeat and Copy Sheet Data for Each Value in Excel and WPS
Question details
The user wants to duplicate a template range on one sheet for every value listed on another sheet, inserting the corresponding source value into a specific column of each newly copied block.
- Product
- Microsoft Excel / WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- Automating the creation of multiple data blocks based on a master template, where each block corresponds to a unique text value from a list on a separate worksheet.
- Observed behavior
- The user recorded a basic macro but it failed to dynamically loop through the list, copy the template block, and insert the respective values into the correct column without hardcoding.
Ensure your workbook is saved as a macro-enabled format (.xlsm) and that you have backed up your original data to prevent accidental overwriting during macro testing.
Write a VBA Macro to Duplicate Templates and Insert Values
A VBA macro is the most robust way to loop through a list in Sheet B, copy the template range in Sheet A, and paste the values sequentially without manual effort.
Since the macro recorder only logs static cell references, it cannot dynamically iterate through a list of values. You must manually add a loop in the VBA editor to repeat the copy-paste action for each item in your source sheet.
This solution works seamlessly in both Microsoft Excel and the desktop versions of WPS Spreadsheet.
Press Alt + F11 on your keyboard to open the Visual Basic for Applications editor, then click Insert > Module to create a blank script.
In your code, define your source and target sheets. For example, assign variables like Set wsA = Sheets("Sheet A") and Set wsB = Sheets("Sheet B").
Write a 'For Each' or 'For...Next' loop to iterate through the populated rows in Sheet B (e.g., A1:A43). This loop will dictate how many times the template is copied.
Inside the loop, use the copy method to select the template block. For instance, write wsA.Range("A1:H9").Copy.
Find the last used row in Sheet A to append the copied block. Then, assign the current iteration's value from Sheet B into the designated cells of column C in the newly pasted block.
Automate Data Copying Using Office Scripts
If you are using Excel on the Web, you can utilize JavaScript-based Office Scripts to duplicate the template and set the required values.
Automate Data Processing with WPS Spreadsheet JS Macros
WPS Spreadsheet natively offers a powerful and modern JavaScript Macro environment (JS Macros), allowing you to seamlessly automate repetitive copying and pasting tasks with code similar to Office Scripts.
- 1. Open Your File: Launch WPS Office and open your spreadsheet document.
- 2. Access the Macro Editor: Go to the 'Tools' tab on the ribbon and click on 'Macros' to open the WPS Macro editor.
- 3. Write the Automation Script: Write a JavaScript or VBA loop to read your source list and use the Range.Copy() method to dynamically duplicate your template block.
- 4. Run the Automation: Execute the macro to automatically generate the duplicated blocks and populate column C seamlessly.
Frequently Asked Questions
Why does my recorded macro only copy the same value every time?
The macro recorder hardcodes the specific cell addresses you click during the recording process. To make the macro dynamic so it iterates through different values, you must edit the VBA code to include a loop (like a For...Next loop) and use variable row counters instead of static cell references.
Can I use formulas instead of a macro to duplicate a template multiple times?
While modern array formulas like =TOCOL and SEQUENCE can duplicate flat data arrays, they cannot physically copy cell formatting, borders, or structurally append new static template blocks. A macro or Office Script is required to duplicate actual formatted templates and insert specific column values dynamically.
How do I find the last used row in VBA to paste my copied template?
You can find the last row dynamically using the VBA snippet: LastRow = Cells(Rows.Count, 1).End(xlUp).Row. Adding 1 to this value ensures your macro always pastes the new template block into the first blank row below the existing data.
Does WPS Office support macros and scripts for automation?
Yes, WPS Office robustly supports VBA macros (in the professional version) and includes its own powerful JavaScript Macro editor (WPS JS Macros) which serves as an excellent, modern alternative to web-based Office Scripts for automating repetitive spreadsheet tasks.
