Excel file size - could macro cause?

[smaca]

First time poster - have checked through previous threads without success but apologies if I've missed an answer to this.

I have a file (call it the Master file), which is about 25MB. I run a macro to copy 6 of the sheets from this file to another file (call it the Backup file). This file is 8MB, but the data in it is minimal and should only be about 100KB. [The live version of the file I am now developing does the same thing and produces a file of 500kb]. By copying out one sheet at a time (of the 6) I have isolated the sheet that causes the excessive file size, but I have no idea why. If the data from that sheet is manually copied and pasted out and saved, it's only 100kb. the code I am using to copy out the 6 sheets is as follows:

'select sheets and move them to new workbook
Sheets(Array("Fixed Offer", "Fixed Offer (annual)", "Pool Pass Through (annual)", "Pool Pass Through", "Secter", "Pricing", "Business Case Form")).Select
Sheets("Pool Pass Through (annual)").Activate
Sheets(Array("Fixed Offer", "Fixed Offer (annual)", "Pool Pass Through (annual)", "Pool Pass Through", "Secter", "Pricing", "Business Case Form")).Copy
Sheets("Pricing").Select
    
'Break links
ActiveWorkbook.BreakLink Name:=Source, Type:=xlLinkTypeExcelLinks

As you can see I break the links as well so that removes one potential cause IMO. The 2nd stage of the process is to copy one of the 6 sheets from the Backup file to another file (call it the Offer file). This could be any one of 4 sheets (and it's not the one I identified as causing the excessive file size. No matter which one is used though it ends up as 7-8MB even though it's a simple sheet of about 50kb.

The sheet that is causing it seems to be bringing some sort of "workbook level" formatting or issue with it, which isn't removed from the file if you delete the sheet that seems to have brought it.

I've checked the obvious things like excess formatting or anything like that.
I'm at my wits end here. Any help greatly appreciated.

 

[xlbo]

Little bit vague I know but it may be that there is some corruption on that sheet...can you create a new sheet in the master workbook and then copy data / formulae / formatting over onto the new sheet, delete the old one and try again?

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[smaca]

I was afraid you'd say that! It's not that practical as there are a lot of controls on it, links to/from other sheets and it would be something approaching a nightmare to have to replicate it in a new sheet...

 

[xlbo]

If it's got a lot of controls / links and it is getting copied a lot, that may be the reason for file bloat / corruption

What kind of controls ? do they have code attached to them? Does the code need to function in the new workbook ?



Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[smaca]

There are about 40 checkboxes, they don't drive any macros but are linked to cells for true/false which is used in other formulas.

There are 8 buttons, all of which have code behind them. The code would need to function in any new reproduced sheet.

As I mentioned before, the original version of this had the same structure, format etc, this version is just bigger (it covers a larger time period) so there are 24 additional rows (a month per row) and 24 additional checkboxes. The original (12 month) version does not have the same bloat, or if it does, it doesn't come with the copied sheet when it's output to a new file.

 

[xlbo]

That, unfortanately would indicate a corrupt worksheet

Rather than copying to a new file, can you save the master as a different name and then delete unnecessary sheets from that instead?

From my experience, copying complex sheets with code attached is a good way of corrupting the sheet / workbook

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[smaca]

That's a good suggestion. Thanks for that Geoff.

 

[xlbo]

Only prob is, if the worksheet is already corrupt, you will still get file bloat.....

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[smaca]

True, but doing it that way does seem better. It's leaving me with a file of 1MB. Bigger than the 100kb it should be but better than 8MB!

 

[xlbo]

Good news!!

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question