VBA & Macro Problems

Create Multiple Copies of an Excel Worksheet with VBA

John WilsonJohn Wilson Nov 18, 2026 868 views

Question details

The user wants to create dozens of copies of an Excel worksheet automatically using VBA.

Create Multiple Copies of an Excel Worksheet with 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.
Before you start

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.

Solution 1Recommended

Run a VBA macro to copy the OG worksheet

Use this macro to enter the number of copies you need; it defaults to 43.

1
Save as a macro-enabled workbook

In Excel, select File > Save As and choose Excel Macro-Enabled Workbook (*.xlsm).

2
Insert a VBA module

Open the Visual Basic Editor from Developer > Visual Basic, then select Insert > Module. On Windows, you can also press Alt+F11.

3
Paste the macro

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

4
Run the macro

Return to Excel, select Developer > Macros, choose CopyOGSheet, and select Run. Enter 43 or another whole number, then select OK.

5
Check and save the copies

Inspect the sheet tabs next to OG and use File > Save to keep the new worksheets and macro.

Run a VBA macro to copy the OG worksheet
Check the source workbook: The macro copies OG from the workbook that contains the code. Each new sheet is inserted immediately after OG, and Excel assigns it a unique name.
Free Microsoft Office alternative

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.

Copy and organize worksheets using familiar sheet-tab controlsOpen and edit Microsoft Excel XLSX filesUse a familiar interface for a smoother transition from ExcelWork with a free, lightweight office suite
QA img-9

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.