Workbook Too Large Won't Open

[wavery]

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?

 

[WassimN]

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.

 

[wavery]

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?

 

[WassimN]

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.

 

[wavery]

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.

 

[MUKESHY12390]

you can follow this link https://www.ablebits.com/office-addins-blog/2017/12/20/very-hidden-sheets-excel/ , explains how to unhide sheet from VBA property.

 

[PhilS2520]

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.

 

[mole999]

Just make sure you have an original safe copy taht isn't being modified