Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Numbering Sheets
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Numbering Sheets
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 InsertModule Paste the code into the white pane that appears Alt + F11 to return to your spreadsheet. To run the code, Alt+F8select the macro nameRun -- Regards Roger Govier "albertmb" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Numbering Sheets
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" wrote: 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 InsertModule Paste the code into the white pane that appears Alt + F11 to return to your spreadsheet. To run the code, Alt+F8select the macro nameRun -- Regards Roger Govier "albertmb" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Numbering Sheets
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. -- Regards Roger Govier "albertmb" wrote in message ... 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" wrote: 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 InsertModule Paste the code into the white pane that appears Alt + F11 to return to your spreadsheet. To run the code, Alt+F8select the macro nameRun -- Regards Roger Govier "albertmb" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Page numbering of multiple sheets | Excel Discussion (Misc queries) | |||
Numbering Sheets incrementally (as in Invoice numbers) | New Users to Excel | |||
Numbering Sheets incrementally (as in Invoice numbers) | Excel Discussion (Misc queries) | |||
Numbering Multiple Sheets | Excel Discussion (Misc queries) | |||
Numbering sheets in workbook | Excel Worksheet Functions |