How do I batch add sheets?

**ilans1** · 11-20-2011, 12:41 PM

I would like to add about 100 sheets to an Excel file, so first sheet would be WO #1, then WO #2......WO #100.

Is there any way to do this as a batch operation?

**arlu1201** · 11-20-2011, 01:14 PM

I created this macro which you can assign to a button:

Sub add_sheets()

For i = 1 To 100
    Worksheets.Add(after:=Worksheets(Worksheets.Count)).Name = "WO #" & i
Next

Worksheets("Sheet1").Delete
Worksheets("Sheet2").Delete
Worksheets("Sheet3").Delete

End Sub

The macro creates 100 blank sheets and i did this in a blank workbook which had the default Sheet1, 2 and 3, which i delete at the end of the code. You can put a quote in front of these lines and it will quit the deleting.

**ilans1** · 11-20-2011, 01:18 PM

Im sorry im new at this. Where do I enter the code you provided, and how do I assign to a button?

Many thanks!

**arlu1201** · 11-20-2011, 01:22 PM

In the excel file, click Alt+F11. This will take you to the VBE Editor. On the left hand side, you will see Microsoft Excel Objects. Right click on it and say Insert-> Module. This will open a blank page on the right hand side. Copy the code there and save. Come back to the excel sheet and go to Developer tab, under Controls, there will be Insert. Select the button type. Insert it on your sheet. You can name it right away. Then right click and say Assign Macro. You will see the name of the macro that i created in the list. Select it. Save the file. You are ready to go.

**ilans1** · 11-20-2011, 03:03 PM

Thanks! Now, if I want all these sheets to contain the same set of data, how would I do that? Basically, I want to copy whats in my first sheet and populate all 100 sheets with the same data.

**arlu1201** · 11-21-2011, 02:00 PM

The code is simple - Very similar to the 1st -

Sub add_sheets()

For i = 1 To 100
   Worksheets("Sheet1").Copy after:=Worksheets(Worksheets.Count)
   ActiveSheet.Name = "WO #" & i
    
Next

End Sub