Number Of Worksheets in an Excel workbook
-
- PlatinumLounger
- Posts: 3811
- Joined: 24 Oct 2010, 23:39
- Location: Canton, Ohio USA
Number Of Worksheets in an Excel workbook
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)?
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
hlewton
-
- GoldLounger
- Posts: 3081
- Joined: 24 Jan 2010, 19:07
Re: Number Of Spreadsheets in an Excel workbook
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.
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.
-
- Administrator
- Posts: 78583
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Number Of Spreadsheets in an Excel workbook
To illustrate what Argus wrote:
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.
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.
Best wishes,
Hans
Hans
-
- PlatinumLounger
- Posts: 3811
- Joined: 24 Oct 2010, 23:39
- Location: Canton, Ohio USA
Re: Number Of Spreadsheets in an Excel workbook
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
hlewton
-
- PlutoniumLounger
- Posts: 15642
- Joined: 24 Jan 2010, 23:23
- Location: brings.slot.perky
Re: Number Of Spreadsheets in an Excel workbook
Code: Select all
Sub MaxWorksheets()
While True
Worksheets.Add
Application.Caption = Worksheets.Count
ThisWorkbook.Save
DoEvents
Wend
End Sub
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.
He who plants a seed, plants life.
-
- Administrator
- Posts: 7215
- Joined: 15 Jan 2010, 22:52
- Location: Middle of England
Re: Number Of Spreadsheets in an Excel workbook
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?
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
-
- PlutoniumLounger
- Posts: 15642
- Joined: 24 Jan 2010, 23:23
- Location: brings.slot.perky
Re: Number Of Spreadsheets in an Excel workbook
So that heinous people who doubt my word can repeat my experiment using the code I used without cutting into my Nina Conti time.What is the advantage of adding your code as an attachment?
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.
He who plants a seed, plants life.
-
- Administrator
- Posts: 7215
- Joined: 15 Jan 2010, 22:52
- Location: Middle of England
Re: Number Of Spreadsheets in an Excel workbook
Pah!
And, isn't it easier for users to Click, Right_Click > Copy?
And, isn't it easier for users to Click, Right_Click > Copy?
You do not have the required permissions to view the files attached to this post.
Leif
-
- PlatinumLounger
- Posts: 3811
- Joined: 24 Oct 2010, 23:39
- Location: Canton, Ohio USA
Re: Number Of Spreadsheets in an Excel workbook
WOW, very interesting with my limited understanding of what I just read.
Regards,
hlewton
hlewton
-
- PlutoniumLounger
- Posts: 15642
- Joined: 24 Jan 2010, 23:23
- Location: brings.slot.perky
Re: Number Of Spreadsheets in an Excel workbook
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
He who plants a seed, plants life.
-
- GoldLounger
- Posts: 3081
- Joined: 24 Jan 2010, 19:07
Re: Number Of Spreadsheets in an Excel workbook
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)?ChrisGreaves wrote: ↑12 Jul 2021, 13:02Well, someone has to check whether or not you guys are telling the truth.
(Well perhaps not since on 2003.)
In the Intel i3 with a small amount of memory-category.ChrisGreaves wrote: ↑12 Jul 2021, 13:02Should that claim fail, I hereby apply for the Eileen's Lounge world record for the greatest number of sheets in an Excel2003 workbook.
Next task, select all files on the data drive and click Enter.
(Ah, there's a limit.)
Byelingual When you speak two languages but start losing vocabulary in both of them.
-
- PlutoniumLounger
- Posts: 15642
- Joined: 24 Jan 2010, 23:23
- Location: brings.slot.perky
Re: Number Of Spreadsheets in an Excel workbook
Bingo! Noone else in Boinavista has a laptop quite like mine!
Cheers
Chris
He who plants a seed, plants life.
-
- GoldLounger
- Posts: 3081
- Joined: 24 Jan 2010, 19:07
Re: Number Of Spreadsheets in an Excel workbook
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.
-
- Administrator
- Posts: 12618
- Joined: 16 Jan 2010, 15:49
- Location: London, Europe
Re: Number Of Spreadsheets in an Excel workbook
I tried running that and it took about 6 hours to add 3,000 worksheets. I stopped it at that stage.
StuartR
-
- GoldLounger
- Posts: 3081
- Joined: 24 Jan 2010, 19:07
Re: Number Of Spreadsheets in an Excel workbook
Number X, or whatever is achieved at the heat death of the universe, will win.
-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 ...
-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.
-
- Administrator
- Posts: 7215
- Joined: 15 Jan 2010, 22:52
- Location: Middle of England
Re: Number Of Spreadsheets in an Excel workbook
I hope nobody took my claim of 55,448 worksheets seriously...
Leif
-
- Administrator
- Posts: 78583
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Number Of Spreadsheets in an Excel workbook
I assumed that you had been shopping around for sheets.
Best wishes,
Hans
Hans
-
- Administrator
- Posts: 7215
- Joined: 15 Jan 2010, 22:52
- Location: Middle of England
-
- Administrator
- Posts: 12618
- Joined: 16 Jan 2010, 15:49
- Location: London, Europe
Re: Number Of Spreadsheets in an Excel workbook
Were they Egyptian cotton, or polyester? It makes a big difference.
StuartR
-
- Administrator
- Posts: 7215
- Joined: 15 Jan 2010, 22:52
- Location: Middle of England