Excel VBA Macro to Copy Rows by Value and Repeat Count
Question details
Create a VBA macro that scans a source column, copies matching rows to a second worksheet, and repeats each copied row according to its value.
- Product
- Microsoft Excel VBA
- Device & OS
- not provided
- Scenario
- The user needs to copy rows from a source worksheet when a specified column contains 100 or 10. Rows with 100 must be repeated 50 times, while rows with 10 must be repeated 3 times.
- Observed behavior
- Matching rows need to be appended to the next available row on the target worksheet with the required repeat count.
Confirm the source and target worksheet names, the column containing the values to inspect, and the target column used to find the next empty row. If your first row contains headers, set the loop to begin at row 2.
Use a VBA macro to copy and repeat matching rows
Use this method when you want Excel to append each matching source row to another worksheet multiple times based on the value in a selected column.
This example checks column H on Sheet1. It copies every row where column H equals 100 fifty times and every row where it equals 10 three times, appending the results to Sheet2. Update the worksheet names and column letters before running the macro.
In Excel, press Alt+F11, then select Insert > Module to create a standard VBA module.
Paste the following code into the module. Change "Sheet1", "Sheet2", "H", and "A" if your workbook uses different sheet names or columns: Sub CopyRowsByValueAndRepeatCount() Const CheckColumn As String = "H" Const TargetLastRowColumn As String = "A" Dim wsSource As Worksheet Dim wsTarget As Worksheet Dim lastSourceRow As Long Dim nextTargetRow As Long Dim sourceRow As Long Dim repeatCount As Long Dim copyNumber As Long Application.ScreenUpdating = False Set wsSource = Worksheets("Sheet1") Set wsTarget = Worksheets("Sheet2") lastSourceRow = wsSource.Cells(wsSource.Rows.Count, CheckColumn).End(xlUp).Row nextTargetRow = wsTarget.Cells(wsTarget.Rows.Count, TargetLastRowColumn).End(xlUp).Row + 1 For sourceRow = 2 To lastSourceRow repeatCount = 0 Select Case wsSource.Cells(sourceRow, CheckColumn).Value Case 100 repeatCount = 50 Case 10 repeatCount = 3 End Select For copyNumber = 1 To repeatCount wsSource.Rows(sourceRow).Copy Destination:=wsTarget.Rows(nextTargetRow) nextTargetRow = nextTargetRow + 1 Next copyNumber Next sourceRow Application.ScreenUpdating = True End Sub
Return to Excel, press Alt+F8, select CopyRowsByValueAndRepeatCount, and select Run. The copied rows are added after the last used row in the target worksheet.
Adjust the repeat rules for additional values
Use this option if more source values need their own repeat counts.
Copy and Repeat Matching Rows with WPS Spreadsheet
WPS Spreadsheet provides a familiar worksheet environment for working with Excel-compatible files and VBA-based automation. Open the workbook, use the VBA tools available in your WPS Office edition, paste the macro, and run it to copy rows according to the selected value and repeat rules.
- 1. Open the macro-enabled workbook: In WPS Spreadsheet, select Menu > Open and choose the XLSM workbook containing the source and target sheets.
- 2. Access the VBA editor: Open the Developer or Tools area and choose Visual Basic, then insert a new module if the workbook does not already contain one.
- 3. Add the copy-and-repeat macro: Paste the VBA code and update the source sheet, target sheet, checked column, and target append column to match your workbook.
- 4. Run the procedure: Open the Macro dialog, select CopyRowsByValueAndRepeatCount, and choose Run to append the repeated rows to the target worksheet.
Frequently Asked Questions
How do I prevent the macro from copying the header row?
Start the source loop at row 2, as shown in For sourceRow = 2 To lastSourceRow. If your headers are on a different row, change 2 to the first row containing data.
Can I check a column other than H?
Yes. Change Const CheckColumn As String = "H" to the letter of the column that contains the values you want to evaluate.
Why does the macro use column A to find the next target row?
The target append position is calculated from TargetLastRowColumn. Use a column that always contains data in the target sheet, then change "A" if another column is more reliable.
How can I add a different repeat count for another value?
Add another entry in the Select Case block, such as Case 25: repeatCount = 10. The macro will copy rows containing 25 ten times.
Will the macro copy formulas and formatting from the source row?
Yes. Using wsSource.Rows(sourceRow).Copy Destination copies the entire row, including cell values, formulas, and formatting, into each target row.
