Auto Numbering Worksheets

[DLee-AB]

I've created a workbook where I will have to copy one worksheet 25 times and each one will be numbered. My first worksheet will be numbered 1000 for example, when I copy that worksheet is there function that would automatically number the next worksheet 1001 and so on to 1025? Thank you for your help.

[Paul]

Hi DLee,

How about just using a macro to do the work for you? See below.

Please Login or Register  to view this content.

[DLee-AB]

Thank you, I copied this macro to my worksheet but when I copy try to copy the worksheet the number doesn't change. Clicking "run" I get a message "Subscript out of Range".

[Paul]

My guess is you either don't have a sheet named 1000, or you already have one or more sheets named 1001 through 1025.

[Leon V (AW)]

are there any sheets in the workbook called 1001, 1002...etc already?

how many sheets are in this workbook?

[MrShorty]

A macro like Paul is suggesting will be the most flexible method.

Here's a non-macro strategy I use that works pretty well for copying sheets.

You've probably noticed that if you copy a sheet named "sheet1", the copy is named "sheet1 (2)". If you copy it again, you get a "sheet1 (3)". Recognizing the pattern, for sheets that I want to increment with each copy, I started including the parentheses ["(1)"] in the first copy of the sheet. So, in your case, I might start with a sheet named "(1000)". When copied, the copy will automatically be called "(1001)", then "(1002)", then "(1003)", and so on. Or maybe "sheet(1)" which copies to "sheet(2)" which copies to "sheet(3)" and so on. If you can tolerate the presence of the parantheses and you are using the copy worksheet command, this might be a simple way to get the autoincrement that you are asking for.

[DLee-AB]

I start out with one sheet, the actual starting number is 10476. (1000 was just an example).

[DLee-AB]

There is only one sheet and I would like 25.

[Paul]

I created a new workbook and deleted all worksheets except Sheet1. I changed the name of Sheet1 to 10476. I right-clicked that sheet tab and chose View Code. I pasted the following code into the VBA Editor and ran it. 25 new sheets were created in sequential numerical order as expected.

Please Login or Register  to view this content.

The only difference between this code and my original code is that I changed 1000 to 10476 in both places.

[DLee-AB]

Thank you Paul, this time it worked.