Hello everyone,

When you create a new sheet in Excell 2007, it calls it Sheet1 and 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 calls new sheets? The reason is, I have a Macro that creates a sheet, and then 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 not "Sheet2", so my Macro throws an error.

If I could change it so that every new sheet was called, for example "new", then my Macro could always reference "new" and rename it to "pete", no matter how many times its run.

Now your probably going to ask why I would want to run this more than once, as I cannot have two sheets called "Pete" anyway. Well, the Macro firstly deletes the sheet called Pete, before doing a bunch of other stuff and lastly creating the sheet again. So there never is a situation where there would be more than one sheet called "Pete".

Is anyone able to help with this? An alternative solution would be rather than delete the "Pete" sheet it just clears all the contents held in it - but the problem is if I do this, the sheet becomes empty as expected but at the point where data is pasted in, the Macro throws a run time error and crashes my entire work book. I think this is a bug in Excel itself as the macro runs fine if the sheet is brand new.

Any help is greatly appreciated.

Many thanks.