Huge file size doesnt make any sense

Skagga5

New Member
Joined
May 23, 2013
Messages
11
Hi All

I have got a VBA model that performs our monthly reporting, I then have a command button on an Userform which allows the user to email four of the sheets in the workbook, will refer to this as the extracted workbook.

The masterfile is 18MB, the extracted workbook that gets emailed is 3MB big, when I copy each individual sheet of the extracted workbook into a new workbook each of the four files are 3MB. If I delete my four sheets in the extracted workbook and I am effectively left with an empty workbook the file size is still 2.7Mb. Somewhere something is hiding that is causing this workbook size to be overly excessive.

Any help on how I can reduce the file size would be greatly appreciated.

regards
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
You can't delete all the sheets in a workbook, so what is left in that extracted workbook, and how exactly was it created in the first place?
 
Upvote 0
I have a master workbook that uses code to extract four sheets and attach them to an email, there are no formulas in the sheets of the extracted workbook and to be honest is very small insignificant worksheets in the extracted workbook, the size of this workbook is 3MB, I tested the size of each sheet and when I extract them to a new workbook they all come in at 3MB individually which is also very strange.
On the extracted workbook, I inserted a blank sheet, sheet 1 and deleted the four sheets with my data, so I only have 1 empty sheet in the extracted workbook, when I save this this the file size is 2.7MB for practically empty workbook.
 
Upvote 0
How did you extract them to a new workbook - what is the code you used?
 
Upvote 0
Set Sourcewb = ActiveWorkbook
Sheets(Array("Office_Summary", "Detail Report", "TransactionalSummary", "Transactional_Data")).Copy
Set Destwb = ActiveWorkbook
 
Upvote 0
That looks OK. Can you post the "empty" workbook somewhere (eg OneDrive, Dropbox) so I can have a look at it?
 
Upvote 0
I can't download either of those, I'm afraid. Did you set permissions correctly for sharing?
 
Upvote 0
I was able to download the file.

The problem is in the defined names. When I saved the xlsb file as an xlsx file, the size was 2.77MB. All of the defined names refer to files on other drives including network drives.

Deleting the named whole-column range "Currency" reduced the size to 1.51MB.

Deleting the named whole-column range "sdf" reduced the size to 15.8KB.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

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