running same macro again to create a new sheet

lezawang

Well-known Member
Joined
Mar 27, 2016
Messages
1,631
Office Version
  1. 365
Platform
  1. Windows
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.
Code:
 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

Code:
Sub Macro1()
'
' Macro1 Macro
'


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

Some videos you may like

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
19,703
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
You can't name 2 sheets the same !
 

lezawang

Well-known Member
Joined
Mar 27, 2016
Messages
1,631
Office Version
  1. 365
Platform
  1. Windows
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
Rich (BB code):
Sheets("Sheet3").Name = "xyz"

but that is not the case. Thank you
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
19,703
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
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......
Code:
Sheets("Sheet2").Select
Also, you don't need to select the sheet to name it !
This line will do it all
Code:
Sheets("Sheet2").Name = "xyz"
 

lezawang

Well-known Member
Joined
Mar 27, 2016
Messages
1,631
Office Version
  1. 365
Platform
  1. Windows
Got it. Thank you so much.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,464
Messages
5,596,288
Members
414,052
Latest member
Dual Showman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top