Sheet Numbering Across Multiple Workbooks

[VenturaFwy]

I have multiple Workbooks, each with VBA code, and I number the sheets sequentially in each workbook. But when I look at the objects in the VBA Project it appears that Excel is keeping a running sequential count of sheets across ALL of the workbooks!

For example, in a particular workbook I may have 30 sheets numbered 1 - 30. But in the Project View sheet number 23 appears as Sheet(948)23.

Why are two different sheet numbers shown and where did that first one come from? The large size of it, 948, looks like it is sequentially numbering ALL the sheets across ALL the workbooks, in addition to the sequential numbers I am applying within each workbook.

I would like to turn off that count across multiple workbooks.

Thx

 

[Artik]

But in the Project View sheet number 23 appears as Sheet(948)23

I don't think that's how you see the name of the sheet. Rather: Sheet948(Sheet23).
The first name is the sheet's code name, while the name in parentheses is the one visible in Excel. The likely cause of the high numbering is that you copied one of the sheets multiple times and changed the “tab” name.
This has nothing to do with multiple workbooks.

Artik

 

[VenturaFwy]

I don't think that's how you see the name of the sheet. Rather: Sheet948(Sheet23).
The first name is the sheet's code name, while the name in parentheses is the one visible in Excel. The likely cause of the high numbering is that you copied one of the sheets multiple times and changed the “tab” name.
This has nothing to do with multiple workbooks.

Artik

Thanks, Artie.

What shows up in Project Explorer is exactly as I showed it. Sheet948(30).

Assume I create a new Workbook for each month and in each month I name the sheets with the day of the month. So a typical Workbook might be named Jan_2025.xlsm and have sheets named 1 - 31.

At the end of January I would typically save that workbook with the new name Feb_2025.xlsm. Then I would delete all the sheets except the first (#1) and save it again. On February 2nd I typically copy the sheet named 1, rename the new sheet 2 and keep doing that sort of thing for the rest of the month. So I end up with two separate workbooks, the first having 31 sheets and the second having 28.

But if I look at the Project Explorer within Workbook Feb_2025.xlsm I see the sheets as Sheet31(1) through Sheet31(28) with no indication of Workbook Jan_2025.xlsm. After many months that first part of the count can get into the dozens. And it is even worse than that! I use Excel VBA for everything from my Checking account to Sudoku puzzles and the Excel application seems to be accumulating the sheet count across numerous workbooks, even those that weren't generated from each other! That count is now well over 2000 and I can't find any way to force the numbering to recycle afresh with each new workbook. <sigh>

 

[HighAndWilder]

Thanks, Artie.

What shows up in Project Explorer is exactly as I showed it. Sheet948(30).

Assume I create a new Workbook for each month and in each month I name the sheets with the day of the month. So a typical Workbook might be named Jan_2025.xlsm and have sheets named 1 - 31.

At the end of January I would typically save that workbook with the new name Feb_2025.xlsm. Then I would delete all the sheets except the first (#1) and save it again. On February 2nd I typically copy the sheet named 1, rename the new sheet 2 and keep doing that sort of thing for the rest of the month. So I end up with two separate workbooks, the first having 31 sheets and the second having 28.

But if I look at the Project Explorer within Workbook Feb_2025.xlsm I see the sheets as Sheet31(1) through Sheet31(28) with no indication of Workbook Jan_2025.xlsm. After many months that first part of the count can get into the dozens. And it is even worse than that! I use Excel VBA for everything from my Checking account to Sudoku puzzles and the Excel application seems to be accumulating the sheet count across numerous workbooks, even those that weren't generated from each other! That count is now well over 2000 and I can't find any way to force the numbering to recycle afresh with each new workbook. <sigh>

Are you able to post an image of Project Explorer showing the sheets?

Can you please update your profile to indicate what version of Excel you are using?

 

[VenturaFwy]

Are you able to post an image of Project Explorer showing the sheets?

Can you please update your profile to indicate what version of Excel you are using?

I have Microsoft Office Home and Student 2019

 

[Artik]

You are inaccurate in your statements. You don't see the difference between Sheet(948)23 and Sheet948(23)? Moreover, there cannot be multiple sheets with the same code names. You gave such an example in post #3

Sheet31(1) through Sheet31(28)

First of all, there is not much to worry about with the increasing number in the code name. Let it grow even to a million.
If it bothers you a lot you can do as below.
1. delete all day sheets except the first one.
2. go to the VBA editor, call the Properties window (keyboard shortcut F4)
3. in Project Explorer, select the sheet with tab name 1, which is probably Sheet948(1).
4. in the Properties window, change the code name of the sheet (the first item in the list) to, for example, shDay_1.
From now on, any copying of a sheet named 1 will increment the code name by 1.

Artik

 

[VenturaFwy]

Thanks, Artik. You are right about my mistake in #3. The code name increments along with the name I assign. I just overlooked it in proofreading my post.

I will try your solution. I haven't been bothered by those other numbers except that they are distracting. But I hadn't found any way to force Excel start the numbering over again with each new Workbook.