Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
replace all formulas with values in multiple worksheets
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 specialvalues one worksheet at a time would be a huge chore. -- JM |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
replace all formulas with values in multiple worksheets
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" wrote: 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 specialvalues one worksheet at a time would be a huge chore. -- JM |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
replace all formulas with values in multiple worksheets
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. -- JM "Joel" wrote: 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" wrote: 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 specialvalues one worksheet at a time would be a huge chore. -- JM |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
replace all formulas with values in multiple worksheets
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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
replace all formulas with values in multiple worksheets
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
formulas for multiple worksheets | Excel Worksheet Functions | |||
Find/Replace in multiple worksheets | Excel Discussion (Misc queries) | |||
Replace soemthing in multiple worksheets | Excel Discussion (Misc queries) | |||
How do I find and replace null values in Excel 2002 worksheets? | Excel Worksheet Functions | |||
changing formulas to values so that they will be recognized by Find and Replace | Excel Discussion (Misc queries) |