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

I'm not all that certain that even doing a Copy | Paste Special using Values
as an option to convert all formulas to their results will result in much
saved file space. You might try that in a COPY of the workbook to see if
there is much difference in file size after it.

Here is some VB code that you could include in a module in the COPY of your
workbook that would do the conversions for you quickly:

Sub CarveInStone()
Dim anySheet As Worksheet
For Each anySheet In ActiveWorkbook.Worksheets
Worksheets(anySheet.Name).Select
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("A1").Select
Application.CutCopyMode = False
Next
End Sub

I call it CarveInStone because it does do away with the formulas. Might
error out if any sheets are protected, so make sure they aren't.

Save the revised file and compare file sizes between the original and the
modified one. Be extra careful, nothing like playing with things like this
to ruin your day if you destroy all the formulas and work in the source file.
I suggest making a .bak file of the unmodified one before starting testing
any of this just in case.

If the file size isn't significantly smaller, look into exporting the
worksheets as .csv files, which will be small when compared to the Excel
worksheet size and can be opened directly in Excel. But you lose formatting
and only one sheet per .csv file.

"Wayne Knazek" wrote:

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

Thanks tons! You've given me some things to try out. Hopefully my next post
here will be to let you know it worked out. :)

"JLatham" wrote:

I'm not all that certain that even doing a Copy | Paste Special using Values
as an option to convert all formulas to their results will result in much
saved file space. You might try that in a COPY of the workbook to see if
there is much difference in file size after it.

Here is some VB code that you could include in a module in the COPY of your
workbook that would do the conversions for you quickly:

Sub CarveInStone()
Dim anySheet As Worksheet
For Each anySheet In ActiveWorkbook.Worksheets
Worksheets(anySheet.Name).Select
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("A1").Select
Application.CutCopyMode = False
Next
End Sub

I call it CarveInStone because it does do away with the formulas. Might
error out if any sheets are protected, so make sure they aren't.

Save the revised file and compare file sizes between the original and the
modified one. Be extra careful, nothing like playing with things like this
to ruin your day if you destroy all the formulas and work in the source file.
I suggest making a .bak file of the unmodified one before starting testing
any of this just in case.

If the file size isn't significantly smaller, look into exporting the
worksheets as .csv files, which will be small when compared to the Excel
worksheet size and can be opened directly in Excel. But you lose formatting
and only one sheet per .csv file.

"Wayne Knazek" wrote:

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

Glad I was able to give you some directions to look in. Sometimes there just
aren't convenient solutions. Probably the optimum solution would be to have
a net accessible location to place the updated file and let people upload it
via simply sending them a link to it in an email. But that's not always
available to people.

Another option to consider is to use a compression utility such as WinRar or
WinZip to compress the file before sending it. I just zipped up a 10MB
Access file and attached the .zip file as a 1.6MB attachment to an email to a
client. With a licensed copy of WinZip you can even create a
'self-extracting' zip file so that the recipient does not even have to have
it on their system to get at the contents of the file. Just another thought.

Good luck with your search for a solution.

"Wayne Knazek" wrote:

Thanks tons! You've given me some things to try out. Hopefully my next post
here will be to let you know it worked out. :)

"JLatham" wrote:

I'm not all that certain that even doing a Copy | Paste Special using Values
as an option to convert all formulas to their results will result in much
saved file space. You might try that in a COPY of the workbook to see if
there is much difference in file size after it.

Here is some VB code that you could include in a module in the COPY of your
workbook that would do the conversions for you quickly:

Sub CarveInStone()
Dim anySheet As Worksheet
For Each anySheet In ActiveWorkbook.Worksheets
Worksheets(anySheet.Name).Select
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("A1").Select
Application.CutCopyMode = False
Next
End Sub

I call it CarveInStone because it does do away with the formulas. Might
error out if any sheets are protected, so make sure they aren't.

Save the revised file and compare file sizes between the original and the
modified one. Be extra careful, nothing like playing with things like this
to ruin your day if you destroy all the formulas and work in the source file.
I suggest making a .bak file of the unmodified one before starting testing
any of this just in case.

If the file size isn't significantly smaller, look into exporting the
worksheets as .csv files, which will be small when compared to the Excel
worksheet size and can be opened directly in Excel. But you lose formatting
and only one sheet per .csv file.

"Wayne Knazek" wrote:

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