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