replace all formulas with values in multiple worksheets

[JM]

I have 4 65MB excel 2003 workbooks, each with 125 sheets of data. Each sheet
has multiple array and non-array formulas filled down for 100 rows x 20
columns. I need to email these workbooks, but when zipped, each is still over
7MB. Is there an easy way to remove all formulas in all worksheets at one
time, but still leave the final calculated values? Using paste special>values
one worksheet at a time would be a huge chore.

 

[Joel]

It is simple. You just need to use PasteSpecial. the problem may be that
excel doesn't like to shrink workbooks once they beome very large. Yo may
need to pust the results into a new workbook to get it to reduce to a very
small size.

1) Select the entire worksheet by typing Cntl-A.
2) Copy selected area by type Cntl-C
3) Go the edit menu and select PasteSpecial. Then choose values and press
OK.

This will return the results onto the current worksheet.

You may want to open a new workbook and pastespecial onto a new worksheet so
the size of the file really shrinks a lot.

 

[JM]

Yes, I am all-too-familiar with replacing formulas in a worksheet using paste
special. However, I have 500 worksheets spread over 4 workbooks. Is there a
way to avoid using paste special 500 times? Thanks.

 

[Max]

In a spare copy, try running this sub

Sub FreezeAllSheets()
Dim anySheet As Worksheet
For Each anySheet In ActiveWorkbook.Worksheets
anySheet.UsedRange.Copy
anySheet.UsedRange.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Next
End Sub

--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik

 

[Joel]

I think it is better to do it in a new workbook. It will make a much smaller
file

Sub MakeCopy()

Set bk = ThisWorkbook
'create new workbook using copy without bnefore or after
bk.Sheets(1).Copy
Set Newbk = ActiveWorkbook
For ShtCount = 2 To bk.Sheets.Count
Set NewSht = Sheets.Add _
(After:=Newbk.Sheets(Newbk.Sheets.Count))
bk.Sheets(ShtCount).Cells.Copy
NewSht.Cells.PasteSpecial _
Paste:=xlPasteValues
Next ShtCount
End Sub

 

[nente002]

Hello Joel,
I too was trying to copy and past value's without succes, so many thanks for the great code! works like a charm.
However can you give a tip on how to keep the original tab names? I have a workbook with 8 worksheets with names like student, staff, teachers, etc
When I run the macro the workbook including all sheets gets copied/pasted into value's however I also need to keep the worksheet names, now in the new worksheet the names are the Excel defaults (sheet1, sheet2, etc)
Is it posible to also copy the names of the worksheets into the new workbook?
Many thanks in advance!

I think it is better to do it in a new workbook. It will make a much smaller
file

Sub MakeCopy()

Set bk = ThisWorkbook
'create new workbook using copy without bnefore or after
bk.Sheets(1).Copy
Set Newbk = ActiveWorkbook
For ShtCount = 2 To bk.Sheets.Count
Set NewSht = Sheets.Add _
(After:=Newbk.Sheets(Newbk.Sheets.Count))
bk.Sheets(ShtCount).Cells.Copy
NewSht.Cells.PasteSpecial _
Paste:=xlPasteValues
Next ShtCount
End Sub




"Max" wrote:

> In a spare copy, try running this sub
>
> Sub FreezeAllSheets()
> Dim anySheet As Worksheet
> For Each anySheet In ActiveWorkbook.Worksheets
> anySheet.UsedRange.Copy
> anySheet.UsedRange.PasteSpecial Paste:=xlPasteValues
> Application.CutCopyMode = False
> Next
> End Sub
>
> --
> Max
> Singapore
> http://savefile.com/projects/236895
> Downloads:27,000 Files:200 Subscribers:70
> xdemechanik
> ---
> "JM" wrote:
> > Yes, I am all-too-familiar with replacing formulas in a worksheet using paste
> > special. However, I have 500 worksheets spread over 4 workbooks. Is there a
> > way to avoid using paste special 500 times? Thanks.