P
pete212
Hello everyone,
When you create a new sheet in Excell 2007, it calls it Sheet1 an
obviously this increments for each new sheet, the next one being Sheet2
Sheet3 etc.
My question is that is there anyway to change the default name it call
new sheets? The reason is, I have a Macro that creates a sheet, and the
renames it. The problem is, I can only run this Macro once.
Sheets.Add After:=Sheets(Sheets.Count)
Sheets("Sheet2").Select
Sheets("Sheet2").Name = "Pete"
If I run this Macro again, then the new sheet is called "Sheet3" and no
"Sheet2", so my Macro throws an error.
If I could change it so that every new sheet was called, for exampl
"new", then my Macro could always reference "new" and rename it t
"pete", no matter how many times its run.
Now your probably going to ask why I would want to run this more tha
once, as I cannot have two sheets called "Pete" anyway. Well, the Macr
firstly deletes the sheet called Pete, before doing a bunch of othe
stuff and lastly creating the sheet again. So there never is a situatio
where there would be more than one sheet called "Pete".
Is anyone able to help with this? An alternative solution would b
rather than delete the "Pete" sheet it just clears all the contents hel
in it - but the problem is if I do this, the sheet becomes empty a
expected but at the point where data is pasted in, the Macro throws
run time error and crashes my entire work book. I think this is a bug i
Excel itself as the macro runs fine if the sheet is brand new.
Any help is greatly appreciated.
Many thanks
When you create a new sheet in Excell 2007, it calls it Sheet1 an
obviously this increments for each new sheet, the next one being Sheet2
Sheet3 etc.
My question is that is there anyway to change the default name it call
new sheets? The reason is, I have a Macro that creates a sheet, and the
renames it. The problem is, I can only run this Macro once.
Sheets.Add After:=Sheets(Sheets.Count)
Sheets("Sheet2").Select
Sheets("Sheet2").Name = "Pete"
If I run this Macro again, then the new sheet is called "Sheet3" and no
"Sheet2", so my Macro throws an error.
If I could change it so that every new sheet was called, for exampl
"new", then my Macro could always reference "new" and rename it t
"pete", no matter how many times its run.
Now your probably going to ask why I would want to run this more tha
once, as I cannot have two sheets called "Pete" anyway. Well, the Macr
firstly deletes the sheet called Pete, before doing a bunch of othe
stuff and lastly creating the sheet again. So there never is a situatio
where there would be more than one sheet called "Pete".
Is anyone able to help with this? An alternative solution would b
rather than delete the "Pete" sheet it just clears all the contents hel
in it - but the problem is if I do this, the sheet becomes empty a
expected but at the point where data is pasted in, the Macro throws
run time error and crashes my entire work book. I think this is a bug i
Excel itself as the macro runs fine if the sheet is brand new.
Any help is greatly appreciated.
Many thanks