How to evaluate the size of each worksheet in a workbook?

[eric.malkowiak]

I have a 30+ worksheets in a workbook that is to big....
I would like to know which one are the top memory eater, so I can
concentrate my effort on these one.

Thanks for your help

 

[David McRitchie]

If you want to know which one try creating the Table of Contents described
in
http://www.mvps.org/dmcritchie/excel/buildtoc.htm
which will tell you how many cells are in the used area.

If you want to just try to fix lastcell problems then run the macro

# Why do my scrollbars go to row 500 -- my data ends in cell E50?,
contextures.com, Debra Dalgleish. The basis of deletions is all columns
without content, and all rows without content. Warning: merged cells
problems -- a merged cell has content only in the upper left cell of a
merged cell group. You could clip off unused parts of merged cells and
cells formatted as merged as in a form. It is important that you format
entire columns because formatting in partially formatted columns will be
lost from the deletion points.
http://www.contextures.com/xlfaqApp.html#Unused

 

[Guest]

The code below will save each worksheet to a sperate file using the sheet
name as the filename. hen compare the filesizes to see which sheets are the
largest.


Sub CopySheets()
'
' Macro2 Macro
' Macro recorded 8/22/2007 by Joel
'

'
Const SavePath = "c:\temp\"
testnumber = 1

For Each ws In Worksheets

Worksheets(ws.Name).Copy
ActiveWorkbook.SaveAs Filename:=SavePath & ws.Name
ActiveWorkbook.Close
testnumber = testnumber + 1

Next ws
End Sub