File Size Problem

[Wayne Knazek]

I have a workbook with about 40 tabs/sheets. Nearly all of them are pulling
data from one source or another. Therefore, nearly all the cells contain an
actual formula. Plus the file is set to 400 rows for each of the sheets/tabs
to allow ample space for the actual entries needed each time the sheet is
used. Later, I'll trim off the excess rows.

The file size is huge! We won't be able to email the reports through our
own system, let alone to the customer.

EX: A). One department does a dimensional layout on a part. There might be
700 to 800 dimensions to report. That all goes into appropriate row/colum in
the Layout sectioin of the workbook. It's formatted/laid out in a logical,
systemmatic way that functions well for someone entering data.

B). Engineering gets that data, but needs to have it in a totally different
format, dictated by the customer. The data isn't even calculated the same
way in many instances, so there is a need for this 2nd format. (2nd section
of sheets) But why type all that data again? Right? So . . .

All the sheets in the 2nd section pull the original layout data to one cell
or another. Sometimes creating helper columns, etc. Then each cell of the
sheet(s) has a formula pulling a result in from somewhere.

C). New workbook . . . (maybe) somehow the info in all the cells of the 2nd
section need to go into a matched workbook, but not as formulae.

Can this be done with VBA????

Hope that makes sense.

What I was thinking was . . . create another workbook that's basically
identical in terms of what goes into it. Headings, etc. would be identical.
Then . . .

Can I have that new entire workbook retrieve all the info from original, but
as actual text or values?

If I use a formula in each cell to pull the data from the matched cell in
original workbook it defeats the purpose.

OR . . .

is there a way to convert all the cells in the 2nd part as text or values
after the spreadsheet is finished?

In other words . . . EX . . .

Sheet 17 in R10C1 would be . . . = ('sheet1'!R10C1)/('sheet1'!R10D1)

Sheet 1, R10C1 might have 0.875 R10D1 might be 31

But what's really in Sheet 17, R10C1 is a formula, not 0.0282.

Phewwww. I'm not sure even "I" understand what I'm asking! LOL

 

[NickHK]

Wayne,
Sounds like you need to look into Copy, PasteSpecial (xlPasteValues).
Once you get the data in the correct format/display etc for each dept do
something like this:

Worksheets(x).Copy 'creates a copy of the sheet in a new WB
with Activesheet
.cells.copy
.PasteSpecial (xlPasteValues)
end with
activeworkbook.saveas New name"

NickHK

 

[Wayne Knazek]

Great idea! Received several possible solutions. Actually because of
learning curve, I'll try them all so I can learn more etc. But I like yours.
Seems to fit my logical mind. LOL

Thanks again! Wayne