running same macro again to create a new sheet

[lezawang]

Hi
I opened a new file. has sheet1. I clicked on recording Macro. I clicked on + to add a new sheet. Then changed sheet name to XYZ. Then stopped the macro. So far so good, Now sheet XYZ is the active one. I now tried to run the same macro but this time, Sheet3 will be created but then I got error message. I checked the code below and I do not understand why it is not working. I thought this line could be an issue.

 Sheets("Sheet2").Name = "xyz"

but what is wrong with renaming the same sheet with same name again? Or could be another line causing the macro can not run again. Thank you so much

Sub Macro1()
'
' Macro1 Macro
'


'
    Sheets.Add After:=ActiveSheet
    Sheets("Sheet2").Select
    Sheets("Sheet2").Name = "xyz"
    Range("C5").Select
End Sub

 

[Michael M]

You can't name 2 sheets the same !

 

[lezawang]

Thank you for your reply. But looking at the code, I do not see that I am trying to name two sheet with the same name.
The first line will create a new sheet, sheet3
then the second line will select sheet 2 (which has name xyz)
then the 3rd line will name sheet 2 as xyz (so I am renaming the sheet again with the same name) so no 2 sheets has the same name so far
I would think that is the case if the code has

Sheets("Sheet3").Name = "xyz"

but that is not the case. Thank you

 

[Michael M]

True
But the second you run your code, it is trying to Select "Sheet2", which no longer exists, as it is now named "xyz"
So you code should error out here......

Sheets("Sheet2").Select

Also, you don't need to select the sheet to name it !
This line will do it all

Sheets("Sheet2").Name = "xyz"

 

[lezawang]

Got it. Thank you so much.