Hello from Steved

I have 2 worksheets named Report Sheet and Drivers information Sheet.

My issue with the below macro is that it produces another sheet named sheet21.

Please I only need the 9 sheets as laid out below. Thankyou.

Sub AddNameNewSheet2()
Dim CurrentSheetName As String
CurrentSheetName = ActiveSheet.Name

Sheets.Add

On Error Resume Next
Worksheets.Add.Name = "Waiheke"
Worksheets.Add.Name = "Panmure"
Worksheets.Add.Name = "Swanson"
Worksheets.Add.Name = "Orewa"
Worksheets.Add.Name = "Shore"
Worksheets.Add.Name = "Wiri"
Worksheets.Add.Name = "Papakura"
Worksheets.Add.Name = "Roskill"
Worksheets.Add.Name = "City"
Do Until Err.Number = 0
Err.Clear
Loop
On Error GoTo 0

Sheets(CurrentSheetName).Select

End Sub

Hoi Steve
if you delete the line Sheets.Add
it should work.
Greetings from Waiau Pa
Bill K

"Steved" wrote in message
...
Hello from Steved

I have 2 worksheets named Report Sheet and Drivers information Sheet.

My issue with the below macro is that it produces another sheet named
sheet21.

Please I only need the 9 sheets as laid out below. Thankyou.

Sub AddNameNewSheet2()
Dim CurrentSheetName As String
CurrentSheetName = ActiveSheet.Name

Sheets.Add

On Error Resume Next
Worksheets.Add.Name = "Waiheke"
Worksheets.Add.Name = "Panmure"
Worksheets.Add.Name = "Swanson"
Worksheets.Add.Name = "Orewa"
Worksheets.Add.Name = "Shore"
Worksheets.Add.Name = "Wiri"
Worksheets.Add.Name = "Papakura"
Worksheets.Add.Name = "Roskill"
Worksheets.Add.Name = "City"
Do Until Err.Number = 0
Err.Clear
Loop
On Error GoTo 0

Sheets(CurrentSheetName).Select

End Sub

This works fine:

Sub AddNameNewSheet2()

Dim CurrentSheetName As String

CurrentSheetName = ActiveSheet.Name

On Error Resume Next

Worksheets.Add.Name = "Waiheke"
Worksheets.Add.Name = "Panmure"
Worksheets.Add.Name = "Swanson"
Worksheets.Add.Name = "Orewa"
Worksheets.Add.Name = "Shore"
Worksheets.Add.Name = "Wiri"
Worksheets.Add.Name = "Papakura"
Worksheets.Add.Name = "Roskill"
Worksheets.Add.Name = "City"

On Error GoTo 0

Sheets(CurrentSheetName).Select

End Sub

RBS


"Steved" wrote in message
...
Hello from Steved

I have 2 worksheets named Report Sheet and Drivers information Sheet.

My issue with the below macro is that it produces another sheet named
sheet21.

Please I only need the 9 sheets as laid out below. Thankyou.

Sub AddNameNewSheet2()
Dim CurrentSheetName As String
CurrentSheetName = ActiveSheet.Name

Sheets.Add

On Error Resume Next
Worksheets.Add.Name = "Waiheke"
Worksheets.Add.Name = "Panmure"
Worksheets.Add.Name = "Swanson"
Worksheets.Add.Name = "Orewa"
Worksheets.Add.Name = "Shore"
Worksheets.Add.Name = "Wiri"
Worksheets.Add.Name = "Papakura"
Worksheets.Add.Name = "Roskill"
Worksheets.Add.Name = "City"
Do Until Err.Number = 0
Err.Clear
Loop
On Error GoTo 0

Sheets(CurrentSheetName).Select

End Sub

Hello from Steved

I thankyou you both.

"Steved" wrote:

Hello from Steved

I have 2 worksheets named Report Sheet and Drivers information Sheet.

My issue with the below macro is that it produces another sheet named sheet21.

Please I only need the 9 sheets as laid out below. Thankyou.

Sub AddNameNewSheet2()
Dim CurrentSheetName As String
CurrentSheetName = ActiveSheet.Name

Sheets.Add

On Error Resume Next
Worksheets.Add.Name = "Waiheke"
Worksheets.Add.Name = "Panmure"
Worksheets.Add.Name = "Swanson"
Worksheets.Add.Name = "Orewa"
Worksheets.Add.Name = "Shore"
Worksheets.Add.Name = "Wiri"
Worksheets.Add.Name = "Papakura"
Worksheets.Add.Name = "Roskill"
Worksheets.Add.Name = "City"
Do Until Err.Number = 0
Err.Clear
Loop
On Error GoTo 0

Sheets(CurrentSheetName).Select

End Sub