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