CODEWITHSUNDEEP
excelvba
Ali Zubair
Ali Zubair

Reputation: 41

Add a sheet after available sheets

I want to add a sheet after total number of sheets available in a workbook.

I declared a variable so user can name new sheet. I have a condition to catch error if user decides not to name it.

sheetName = InputBox("Enter new sheet name.")
If sheetName = "" Then
    Sheets.Add After:=ActiveSheet    
Else
    Sheets.Add(After:=Sheets(Sheets(Sheets.Count).Name)).Name = sheetName
End If

Above code adds a sheet after active sheet.

I have tried replacing

Sheets.Add After:=ActiveSheet

With

Sheets.Add After:=Sheets(Sheets.Count).Name

I also tried worksheet object but does not work.

Upvotes: 0

Views: 776

Answers (1)

MGP
MGP

Reputation: 2551

When using .Add After:= you have to specify the Worksheet-Object you want it to add after, not the name. So use:

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

This adds, every time it is called, a new worksheet at the last position.

If you want to change the Name of the new Worksheet created you could use:

Dim ws As Worksheet

Set ws = Worksheets.Add(After:=Worksheets(Worksheets.Count))

ws.Name = "New Worksheet Name"

Upvotes: 2

Related Questions