Renaming Worksheets to Original Name
Posted by nick on May 09, 2001 5:11 AM
Can you please tell me the code to use in excel 97 to rename each new worksheet created as a result of running the same macro again (eg Data (2)) to the original worksheet name (eg Data ) , because the name will change with the insertion of sheet number each time I run the Macro. (I am using the macro to insert a new sheet and name it).
Posted by Dave Hawley on May 09, 2001 7:23 AM
Hi Nick
Not too sure i follow, but:
Sheets.Add().name="Data"
Will add a new sheet and name it.
If Excel is automatically naming a copied sheet "Data (2)" it is because there is already a sheet called "Data" and you cannot have two sheets with the same name within the same Workbook.
..You can pass the name of a particular sheet to a Stribg variable later on like:
Dim sShtName as String
sShtName =Sheet1.name
You should really always refer to a sheet by it's CodeName as this wont change if the sheet is renamed or moved within the same Workbook. The CodeName can be seen in the Sheets "Property Window" or in the "Project Explorer". In the "Project Explorer" it's the name NOT in brackets.
Dave
OzGrid Business Applications
