Sponsored Link

There was an error in this gadget

Sponsored Link

Tuesday, July 28, 2009

VBA code to Add worksheet

Today we will discuss a different method in VBA to add worksheet in MS Excel workbook. Now when I say different method I mean ways of adding worksheet based on your requirement. Let’s moved ahead and start with simple piece of code to complex one in steps and based on scenario.

Scenario 1: Add worksheet with default name.

Sub Addsheet()

Worksheets.Add

End Sub

Note: The above code will add a worksheet before active worksheet.

Scenario 2: Add worksheet with specific name.

Sub Addsheet()

Worksheets.Add().Name = "Sarfaraz"

End Sub

Scenario 3: Add multiple worksheets.

Sub Addsheet3()

'Add Multiple worksheets

Worksheets.Add After:=Worksheets(Worksheets.Count), Count:=4

End Sub

Scenario 4: Add worksheets at end.

Sub Addsheet4()

'Add worksheets at the end

Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "Sarfaraz"

End Sub

If you have any issue with replicating this code in MS Excel than download the Microsoft Excel file which I have uploaded. To view the code press Alt + F11 key on keyboard. To run the code I have place four buttons. Each button has one scenario.

Download Add Sheet example

For Free Microsoft Excel and VBA help subscribe to blog via email.

We assure you knowledge, not SPAM!

2 comments:

JP said...

You probably want to check if the workbook structure is protected:

Sub Addsheet()

If Not ActiveWorkbook.ProtectStructure Then
Worksheets.Add
End If

End Sub

Bruce Willis said...

I truly liked this function , can you post more?

Post a Comment