Workbook Too Large Won't Open

wavery

New Member
Joined
Jun 29, 2018
Messages
25
Hello,
I have a workbook that I use for "Performance" tracking of our machines. I currently have multiple worksheets in this workbook. I have tab for graphs with 15 different graphs on each tab. That is 120 graphs, these graphs update automatically when data is entered using "Name Manger" with an "Offset" formula. I also have 13 tabs that house different reports. These reports gather their data using "SUMIFS" formulas from a table named "DATADump". I also have a tab called "Graphs And Reports" that has macro buttons to navigate through all the different tabs so I don't have to scroll across the bottom to find tab. All tabs are closed and hidden except "DATADump" and "GraphData". I am now having trouble with the workbook not wanting to open. Is there a way to split this workbook up into three workbooks, 1) Data Warehouse, 2) Graph's, 3)Reports?
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
wavery,

The thread title indicates that you are having trouble opening this particular workbook. Is that the case? I would suggest that you may want to save the workbook as a binary version with an extension .XLSB if and when you open it.

I do also suggest that you limit the number of objects in one worksheet to a logical order and not put everything in one worksheet.

Once you get to open this workbook, you will need to edit some formulas that will now need to point to a new workbook with all the data in it. but yes you can simply move, or copy worksheets to a new workbook and then have that logical breakdown that you mentioned. To do so, right-mouse click on a worksheet tab and look at the context sensitive menu and see what options you have there to do just that.

Also, some code edit maybe necessary to make sure the code knows where it needs to get "stuff" now that they are all in different workbooks.

Hope this helps.
 
Upvote 0
Thank you for your response. When I move existing sheets to a new workbook is there a way to update all of the formulas or will I have to do each one?
 
Upvote 0
It depends. Some might update automatically, others may not. I suggest that you do a comprehensive review of the workbook before attempting this kind of "surgery".

But you did not answer my main question: Can you open the workbook? This is essential if you can't open the workbook, then there are things that you can do to get the data.
 
Upvote 0
I got the workbook open. I noticed that there are a lot of sheets listed in the VBA Project list that don't exist in this workbook.
 
Upvote 0
Hi wavery,
Ok, taking a deep breath! Now that you have the workbook open save it as a .xlsb binary file, and then multiple copies of it with some worksheets and chart sheets removed to make multiple smaller files. This will get you all the “stuff” than once you need you can recreate the original workbook as is now.

Then look at these web pages:
http://www.excelefficiency.com/reduce-excel-file-size/

https://professor-excel.com/how-to-reduce-the-file-size-of-an-excel-workbook/

https://m.wikihow.com/Reduce-Size-of-Excel-Files

or you can search for similar web resources.
 
Upvote 0
Just make sure you have an original safe copy taht isn't being modified
 
Upvote 0

Forum statistics

Threads
1,213,560
Messages
6,114,306
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top