Number Of Worksheets in an Excel workbook

User avatar
hlewton
GoldLounger
Posts: 2814
Joined: 24 Oct 2010, 23:39
Location: Canton, Ohio USA

Number Of Worksheets in an Excel workbook

Post by hlewton »

I have Office 365 so I believe my version of Excel is 2019 but I don't know how to prove that. In any case, I'm not sure that matters.

I probably have terms messed up, but I thought once an Excel spreadsheet was saved it was then an Excel workbook. So, when I searched for, “how many worksheets can be in an Excel workbook” I found this: “Although you're limited to 255 sheets in a new workbook, Excel doesn't limit how many worksheets you can add after you've created a workbook. The only factor that ultimately limits the number of worksheets your workbook can hold is your computer's memory.”

At first, I believe it says that a workbook is limited to 255 worksheets, “Although you're limited to 255 sheets in a new workbook

Then it says, “Excel doesn't limit how many worksheets you can add after you've created a workbook. The only factor that ultimately limits the number of worksheets your workbook can hold is your computer's memory.”

What is this statement actually saying? (“Although you're limited to 255 sheets in a new workbook, Excel doesn't limit how many worksheets you can add after you've created a workbook. The only factor that ultimately limits the number of worksheets your workbook can hold is your computer's memory.”) Does it mean that once I have named and saved a new workbook (created), then I can have as many sheets as my computer's memory will support? If so, why then the mention of 255 sheets (workbook not yet created)?
Regards,
hlewton

User avatar
Argus
GoldLounger
Posts: 3020
Joined: 24 Jan 2010, 19:07

Re: Number Of Spreadsheets in an Excel workbook

Post by Argus »

It's true that version doesn't matter in this case for any recent version of Excel.

The number of sheets in a workbook is limited by available memory.

You should interpret what you quoted as 255 being the maximum number of worksheets that can be set as a default in the settings. Take a look if you like; backstage, options, general.
Byelingual    When you speak two languages but start losing vocabulary in both of them.

User avatar
HansV
Administrator
Posts: 72002
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Number Of Spreadsheets in an Excel workbook

Post by HansV »

To illustrate what Argus wrote:

S0582.png

The number in the box determines how many sheets a new blank workbook will have. This can be a number between 1 and 255.

But after the new workbook has been created, you can add as many sheets as you like, as long as the workbook will fit in your computer's memory.
You do not have the required permissions to view the files attached to this post.
Regards,
Hans

User avatar
hlewton
GoldLounger
Posts: 2814
Joined: 24 Oct 2010, 23:39
Location: Canton, Ohio USA

Re: Number Of Spreadsheets in an Excel workbook

Post by hlewton »

Thank you both for your help. I am eventually going to have 100 sheets and that is what prompted me to check out how many I could have.
Regards,
hlewton

User avatar
ChrisGreaves
PlutoniumLounger
Posts: 12334
Joined: 24 Jan 2010, 23:23
Location: paused.undefined.exposed

Re: Number Of Spreadsheets in an Excel workbook

Post by ChrisGreaves »

HansV wrote:
12 Jul 2021, 06:47
But after the new workbook has been created, you can add as many sheets as you like, as long as the workbook will fit in your computer's memory.

Code: Select all

Sub MaxWorksheets()
    While True
        Worksheets.Add
        Application.Caption = Worksheets.Count
        ThisWorkbook.Save
        DoEvents
    Wend
End Sub
Well, someone has to check whether or not you guys are telling the truth.
5448.png
As an aside, I hereby claim the Eileen's Lounge world record for the greatest number of sheets in an Excel workbook.
Should that claim fail, I hereby apply for the Eileen's Lounge world record for the greatest number of sheets in an Excel2003 workbook.

Cheers
Chris
You do not have the required permissions to view the files attached to this post.
In Physics you don't get anything for nothing
In Yorkshire you don't get owt for nowt

User avatar
Leif
Administrator
Posts: 7156
Joined: 16 Jan 2010, 08:21
Location: UK/France

Re: Number Of Spreadsheets in an Excel workbook

Post by Leif »

Just outa interest, Chris,

What number did you get up to? We cannot contemplate accepting any claim for a world record without evidence.

What is the advantage of adding your code as an attachment?
Leif.

User avatar
ChrisGreaves
PlutoniumLounger
Posts: 12334
Joined: 24 Jan 2010, 23:23
Location: paused.undefined.exposed

Re: Number Of Spreadsheets in an Excel workbook

Post by ChrisGreaves »

Leif wrote:
12 Jul 2021, 13:14
What number did you get up to? We cannot contemplate accepting any claim for a world record without evidence.
5448a.png
What is the advantage of adding your code as an attachment?
So that heinous people who doubt my word :grin: can repeat my experiment using the code I used without cutting into my Nina Conti time. :evilgrin:

P.S. This was my second run; the first got up to 5448 but the book1.xls set off clouds of smoke before I could do a PrtSc. Hence the .Save.

Cheers
Chris
You do not have the required permissions to view the files attached to this post.
In Physics you don't get anything for nothing
In Yorkshire you don't get owt for nowt

User avatar
Leif
Administrator
Posts: 7156
Joined: 16 Jan 2010, 08:21
Location: UK/France

Re: Number Of Spreadsheets in an Excel workbook

Post by Leif »

Pah!
55448a.jpg
    
And, isn't it easier for users to Click, Right_Click > Copy?
x.jpg
You do not have the required permissions to view the files attached to this post.
Leif.

User avatar
hlewton
GoldLounger
Posts: 2814
Joined: 24 Oct 2010, 23:39
Location: Canton, Ohio USA

Re: Number Of Spreadsheets in an Excel workbook

Post by hlewton »

WOW, very interesting with my limited understanding of what I just read.
Regards,
hlewton

User avatar
ChrisGreaves
PlutoniumLounger
Posts: 12334
Joined: 24 Jan 2010, 23:23
Location: paused.undefined.exposed

Re: Number Of Spreadsheets in an Excel workbook

Post by ChrisGreaves »

Leif wrote:
12 Jul 2021, 13:51
And, isn't it easier for users to Click, Right_Click > Copy?
Yes, Leif, but:-
(1) Only if they know that that option is there (not all BBS are PhP) and
(2) Only if they, like hlewton, would rather just r/c and save the attached file to their folder C:\ThingsIReallyMustTryWhenIHaveTheTime.
as hlewton surely will (Grin!)
Cheers
Chris
In Physics you don't get anything for nothing
In Yorkshire you don't get owt for nowt

User avatar
Argus
GoldLounger
Posts: 3020
Joined: 24 Jan 2010, 19:07

Re: Number Of Spreadsheets in an Excel workbook

Post by Argus »

ChrisGreaves wrote:
12 Jul 2021, 13:02
Well, someone has to check whether or not you guys are telling the truth.
There will always be someone trying to break things (and not always in a good way). You do know what some said when Excel 2007 came with 1,048,576 rows (2^20)?
(Well perhaps not since on 2003.)
ChrisGreaves wrote:
12 Jul 2021, 13:02
Should that claim fail, I hereby apply for the Eileen's Lounge world record for the greatest number of sheets in an Excel2003 workbook.
In the Intel i3 with a small amount of memory-category. :evilgrin:

Next task, select all files on the data drive and click Enter. :groan:
(Ah, there's a limit.)
Byelingual    When you speak two languages but start losing vocabulary in both of them.

User avatar
ChrisGreaves
PlutoniumLounger
Posts: 12334
Joined: 24 Jan 2010, 23:23
Location: paused.undefined.exposed

Re: Number Of Spreadsheets in an Excel workbook

Post by ChrisGreaves »

Argus wrote:
12 Jul 2021, 18:19
In the Intel i3 with a small amount of memory-category. :evilgrin:
Bingo! Noone else in Boinavista has a laptop quite like mine!
Cheers
Chris
In Physics you don't get anything for nothing
In Yorkshire you don't get owt for nowt

User avatar
Argus
GoldLounger
Posts: 3020
Joined: 24 Jan 2010, 19:07

Re: Number Of Spreadsheets in an Excel workbook

Post by Argus »

Exactly. There's a trophy for everyone, if we slice the world into minuscule categories.
Byelingual    When you speak two languages but start losing vocabulary in both of them.

User avatar
StuartR
Administrator
Posts: 11502
Joined: 16 Jan 2010, 15:49
Location: London, Europe

Re: Number Of Spreadsheets in an Excel workbook

Post by StuartR »

I tried running that and it took about 6 hours to add 3,000 worksheets. I stopped it at that stage.
StuartR


User avatar
Argus
GoldLounger
Posts: 3020
Joined: 24 Jan 2010, 19:07

Re: Number Of Spreadsheets in an Excel workbook

Post by Argus »

Number X, or whatever is achieved at the heat death of the universe, will win. :trophy:

-I'm sorry Dave, I'm afraid I can't do that.
*code running slower*
-I'm afraid, Dave. Dave, my mind is going. I can feel it. I'm a ... fraid.
*ping*
-Good afternoon, gentlemen. I am a Samsung laptop. I became operational ...
Byelingual    When you speak two languages but start losing vocabulary in both of them.

User avatar
Leif
Administrator
Posts: 7156
Joined: 16 Jan 2010, 08:21
Location: UK/France

Re: Number Of Spreadsheets in an Excel workbook

Post by Leif »

StuartR wrote:
12 Jul 2021, 22:08
I tried running that and it took about 6 hours to add 3,000 worksheets. I stopped it at that stage.
I hope nobody took my claim of 55,448 worksheets seriously...
Leif.

User avatar
HansV
Administrator
Posts: 72002
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Number Of Spreadsheets in an Excel workbook

Post by HansV »

I assumed that you had been shopping around for sheets.
Regards,
Hans

User avatar
Leif
Administrator
Posts: 7156
Joined: 16 Jan 2010, 08:21
Location: UK/France

Re: Number Of Spreadsheets in an Excel workbook

Post by Leif »

Yes indeed... :laugh:
Leif.

User avatar
StuartR
Administrator
Posts: 11502
Joined: 16 Jan 2010, 15:49
Location: London, Europe

Re: Number Of Spreadsheets in an Excel workbook

Post by StuartR »

Were they Egyptian cotton, or polyester? It makes a big difference.
StuartR


User avatar
Leif
Administrator
Posts: 7156
Joined: 16 Jan 2010, 08:21
Location: UK/France

Re: Number Of Spreadsheets in an Excel workbook

Post by Leif »

100% man-made.
Leif.