VBA & Macro Problems

How to Create an Excel VBA Macro to Repeat Items by Copy Count

Steven Parker Oct 15, 2026 870 views

Question details

The user needs an Excel VBA macro to duplicate item names based on a specified copy count.

How to Create an Excel VBA Macro to Repeat Items by Copy Count

Product: Excel
Device & OS: not provided
Scenario: Automating data duplication where items in column A need to be repeated a specific number of times based on corresponding numerical count values in column B.
Observed behavior: The user requires a dynamic script that loops through source rows to output repeated items without hard-coding the limits or list lengths.

Before you start

Ensure that the Developer tab is enabled in your spreadsheet application and that your workbook is saved in a macro-enabled format (.xlsm) before writing or executing VBA code.

Solution 1Recommended

Create a Dynamic VBA Macro Loop

Use a VBA loop to read items and their corresponding copy counts dynamically, then output them to a new column without hard-coding the list length.

To achieve dynamic data duplication, the macro must identify the last active row to accommodate lists of any length. It then uses a primary loop to iterate through the source rows and a nested loop to output the item based on the copy count.

It is essential to validate the copy count before writing the output to prevent infinite loops or errors if a count is blank or zero.

Open the Visual Basic Editor

Navigate to the Developer tab on your Excel ribbon and click on 'Visual Basic', or simply press Alt + F11 on your keyboard to open the VBA editor.

Insert a New Module

In the VBA editor, right-click on your workbook name in the Project Explorer pane on the left, hover over 'Insert', and select 'Module'.

Write the Dynamic Loop Logic

In the new module, write a script that defines the last row using 'End(xlUp).Row'. Create a 'For' loop to iterate through column A. Inside this loop, read the count from column B and use an 'If' statement to verify the count is greater than zero.

Add the Nested Output Loop

Inside the condition, add a nested 'For' loop that runs from 1 to the copy count. Within this nested loop, write the item name to the next available row in your designated output column (e.g., column C), then increment your output row counter.

Run the Macro

Close the VBA editor, return to your worksheet, and press Alt + F8. Select your newly created macro from the list and click 'Run' to populate the repeated items.

Dynamic Range Validation: By utilizing 'End(xlUp).Row', your macro will automatically adjust to accommodate new data added to columns A and B without requiring any code updates.

Efficient Data Processing with WPS Spreadsheet

Automate Data Entry with Macros in WPS Spreadsheet

WPS Spreadsheet features a powerful built-in Macro editor, allowing you to run VBA scripts seamlessly. You can easily automate repetitive tasks like repeating items by copy count in a lightweight and intuitive environment.

1. Access the Macro Tools: Open your dataset in WPS Spreadsheet, navigate to the 'Tools' tab on the top ribbon, and click on 'Macro'.
2. Open Visual Basic Editor: Select 'Visual Basic Editor' from the drop-down menu to open the scripting environment.
3. Insert the Code: Insert a new module and paste your VBA loop script designed to read column A and duplicate it based on the count in column B.
4. Execute the Script: Run the macro directly from the editor or the Macro menu to automatically generate your duplicated list in seconds.

Highly compatible with Microsoft Excel VBA scripts and macrosBuilt-in visual basic editor for seamless workflow automationLightweight program size for fast spreadsheet processingFree to use for everyday office data management tasks

microsoft office alternative - wps office

Frequently Asked Questions

Why is my VBA macro throwing an error when the copy count is blank?

If column B contains blank or text values, the loop might attempt to process an invalid count format. Always add a validation step in your code, such as 'If IsNumeric(count) And count > 0 Then', before executing the nested output loop.

Can I output the repeated items into rows instead of columns?

Yes. To output across columns instead of down a single column, adjust the cell referencing in your nested output loop to increment the column index (e.g., 'Cells(outputRow, outputColumn)') while keeping the row index constant.

Will this macro work if my list length changes daily?

Yes, provided you avoid hard-coding the row limit. By using the 'Cells(Rows.Count, 1).End(xlUp).Row' method to find the last populated row dynamically, the macro will adapt to lists of any length.

How do I ensure my VBA macro is saved for future use?

When saving your document, you must choose 'Save As' and select the 'Excel Macro-Enabled Workbook (*.xlsm)' format. Standard .xlsx files will automatically remove any VBA code upon closing.

Excel & Data Problems