Numbering Sheets

[Guest]

Hi Everyone,
I have a work book with over 300 sheets. All sheets are practically an
exact copy so what I do is 'copy sheets', by default the sheet numbers would
show the previous sheet number as a copy (1(2),1(3)) is there a way where
sheet numbers would go consecutive automatically, i.e. 1,2,3,4,etc.

Thank you for your continuos help.
Regards
Albert

 

[Roger Govier]

Hi Albert

There is no way of changing the behaviour of the naming process when copying
sheets in this manner.

You could use VBA code to run through the file after you have done your
copying, and have the code do the renaming.
The code is shown below

Option Explicit

Sub RenameSheets()

Dim ws As Worksheet, wsname As String
Dim i As Long, j As Long

On Error Resume Next
Application.ScreenUpdating = False
For Each ws In Worksheets
ws.Activate
wsname = ws.Name
i = InStr(wsname, "(")
j = InStr(wsname, ")")

If i <> 0 Then
ws.Name = "Sheet" & Mid(wsname, i + 1, j - 1 - i)
End If
Next

On Error GoTo 0
Application.ScreenUpdating = True
End Sub

Note: There is an On error resume next to ensiure the program does not crash
if you have any other sheets in the workbook of the same name, for example,
if you still had Sheet2 and Sheet3 present. It will skip over Sheet1 (2) and
Sheet1 (3) and not rename them. Best to delete Sheets 2 and 3 before you
start.

To use the code
Copy the code as posted above
Press Alt + F11 to bring up the Visual Basic Editor
Insert>Module
Paste the code into the white pane that appears
Alt + F11 to return to your spreadsheet.

To run the code, Alt+F8>select the macro name>Run

 

[Guest]

Hi Roger,

Thank you for your interest. I am afraid this did not work. When I run the
macro it goes through all the sheets but nothing changes. Could it be that I
am doing something wrong?

Thanks again
Albert

 

[Roger Govier]

Hi Albert

Sorry but I have not been on-line for a long period as my mother was taken
into hospital.
It worked fine for me in my trial.

if you cannot get it to work, send me a copy of your workbook and I will set
it up for you.
to send direct mail to
roger at technology4u dot co dot uk
Do the obvious with at and dots.