Create Multiple Copies of an Excel Worksheet with VBA
Question details
The user wants to create dozens of copies of an Excel worksheet automatically using VBA.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Copy a worksheet named OG multiple times, including an example with 43 copies.
- Observed behavior
- The desired result is a workbook containing the original worksheet and the requested number of copies.
Open the workbook in desktop Excel and confirm that the source sheet is named OG. If the workbook structure is protected, remove that protection from the Review tab before copying sheets.
Run a VBA macro to copy the OG worksheet
Use this macro to enter the number of copies you need; it defaults to 43.
In Excel, select File > Save As and choose Excel Macro-Enabled Workbook (*.xlsm).
Open the Visual Basic Editor from Developer > Visual Basic, then select Insert > Module. On Windows, you can also press Alt+F11.
Paste this code into the module: Sub CopyOGSheet() Dim copies As Variant Dim i As Long copies = Application.InputBox("How many copies of OG?", "Copy worksheet", 43, Type:=1) If VarType(copies) = vbBoolean Then Exit Sub If copies < 1 Or copies > 1000 Or copies <> Fix(copies) Then MsgBox "Enter a whole number from 1 to 1000." Exit Sub End If For i = 1 To CLng(copies) ThisWorkbook.Worksheets("OG").Copy After:=ThisWorkbook.Worksheets("OG") Next i End Sub
Return to Excel, select Developer > Macros, choose CopyOGSheet, and select Run. Enter 43 or another whole number, then select OK.
Inspect the sheet tabs next to OG and use File > Save to keep the new worksheets and macro.

Manage and copy worksheets with WPS Spreadsheet
WPS Office is a free, lightweight alternative with a familiar spreadsheet interface. Open compatible Excel files and work with sheet tabs as you move between office suites. Excel VBA macro support may vary by WPS edition and configuration.

Frequently Asked Questions
How do I make exactly 43 copies?
Run CopyOGSheet from Developer > Macros and accept the default value of 43. The original OG sheet remains in the workbook.
What if my worksheet has a different name?
In the VBA editor, replace both instances of "OG" in the Worksheets references with your exact sheet name before running the macro.
Why can't the macro find the OG sheet?
Confirm that the sheet is named OG and that the macro is stored in the same workbook. ThisWorkbook refers to the workbook containing the VBA code.
Why can't Excel add the copied sheets?
Check Review > Protect Workbook to see whether the workbook structure is protected. Remove the protection if you have permission, then run the macro again.
Can I run this macro in Excel for the web?
No. Open the workbook in desktop Excel to edit and run VBA macros, and save it as an XLSM file to retain the code.




