Excel VBA Macro to Copy Formatted Row Groups with a Button
Question details
Create a button macro that repeatedly copies a three-row template with its formatting and dropdowns, leaving one blank row between groups.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- The template is in rows 9–11, and each button click should add another copy after the preceding group.
- Observed behavior
- Each copied group should retain the template's formatting and data validation and be separated from the previous group by one blank row.
Enable the Developer tab in Excel and confirm that the template fits within A9:J11. This method requires A11 to contain a value, no unrelated data below the template in column A, and empty destination rows.
Assign a VBA macro to copy each group into the next blank position
Use this method to copy the template into empty rows without shifting existing worksheet rows.
The macro finds the last used cell in column A and starts the next group two rows below it. Because A11 contains a value, the first copy starts at row 13, leaving row 12 blank. Later copies follow the same pattern.
The macro checks columns A–J for values or formulas in the separator and destination rows before copying. Existing formatting or validation in otherwise empty cells may still be overwritten.
In Excel, open Developer > Visual Basic. In the VBA editor, select your workbook and choose Insert > Module.
Paste the following code into the module. Replace Sheet1 with your worksheet name and adjust A9:J11 if your template uses different columns. If you change the template size, also update the destination check and spacing logic. Sub CopyRowGroup() Dim ws As Worksheet Dim lastRow As Long Dim nextRow As Long Set ws = ThisWorkbook.Worksheets("Sheet1") lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row nextRow = lastRow + 2 If Application.CountA(ws.Range(ws.Cells(nextRow - 1, 1), ws.Cells(nextRow + 2, 10))) > 0 Then MsgBox "The separator or destination rows contain data. Clear them before copying." Exit Sub End If ws.Range("A9:J11").Copy Destination:=ws.Cells(nextRow, 1) Application.CutCopyMode = False End Sub
Return to the worksheet, select Developer > Insert > Button under Form Controls, and draw the button. In the Assign Macro dialog, select CopyRowGroup and choose OK.
Use File > Save As to save the workbook as an Excel Macro-Enabled Workbook (.xlsm). Click the button twice and verify that the groups appear in rows 13–15 and 17–19, with their formatting and dropdowns intact.

Work with formatted spreadsheets in WPS Office
WPS Office is a free, lightweight alternative with a familiar interface for opening and editing Excel spreadsheets. Move compatible files into WPS Spreadsheets and work with formatted tables and dropdowns. This does not fix Excel VBA issues, and macro support may vary by WPS edition and platform.

Frequently Asked Questions
Why must cell A11 contain a value?
The macro uses the last used cell in column A to find the end of the previous group. A value in A11 ensures that the first copy starts at row 13 and that each copied group ends at its third row.
Does the macro insert new worksheet rows?
No. It copies A9:J11 into existing empty rows and stops if the separator or destination contains values or formulas. It does not shift other cells down.
Will the copied rows keep their dropdown lists?
Excel normally copies data validation with the range. If a copied dropdown behaves differently, select its cell and open Data > Data Validation to check whether relative references changed.
Why is the button blocked from copying a group?
The macro stops when it finds a value or formula in columns A–J of the separator or destination rows. Check those cells and clear or move their contents before clicking the button again.
How do I keep the macro when saving the workbook?
Choose File > Save As and select Excel Macro-Enabled Workbook (.xlsm). When reopening the file, enable macros only if you trust the workbook.




