Charles

I must apologise for that e-mail sent on Friday with all the question marks:
I don't how it ended up like that: most bizarre. (Like I went to sleep
pressing the "?" key !)

Many thanks for coming back with version 2 - it is really good. Much
appreciated.

Regards

Tim



"Charles Williams" wrote in message
...
???????????????????? maybe you want something a bit more tested
??????????????
- watch for line wrap

Option Explicit
Sub SheetSizer()

' determine the filesize of each sheet in a workbook
' Charles Williams 25/7/2003

Dim oBk As Workbook
Dim oSht As Worksheet
Dim strSheetname As String
Dim jSize As Long

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.CalculateBeforeSave = False
Application.DisplayAlerts = False

ActiveWorkbook.SaveCopyAs FileName:="BigBook.Bak"
ActiveWorkbook.SaveAs FileName:="BigBook.xls"

Set oBk = Workbooks("bigBook.xls")
jSize = FileLen(oBk.FullName)

Debug.Print "Book " & CStr(jSize \ 1024) & "KB"

On Error Resume Next
For Each oSht In oBk.Worksheets
strSheetname = oSht.Name
oSht.Delete
oBk.Save
If oBk.Worksheets.Count 1 Then
Debug.Print "Sheet " & strSheetname & " " & CStr((jSize -
FileLen(oBk.FullName)) \ 1024) & "KB"
End If
jSize = FileLen(oBk.FullName)
Next oSht

Debug.Print "Sheet " & strSheetname & " " & CStr((jSize) \ 1024) &
"KB"

Set oSht = Nothing
Set oBk = Nothing
End Sub


regards
Charles
______________________
Decision Models
The Excel Calculation Site
www.DecisionModels.com
"Tim Childs" wrote in message
...

?????????????????????????????????????????????????? ??????????????????????????

?????????????????????????????????????????????????? ??????????????????????????

?????????????????????????????????????????????????? ??????????????????????????

?????????????????????????????????????????????????? ??????????????????????????

?????????????????????????????????????????????????? ??????????????????????????

?????????????????????????????????????????????????? ??????????????????????????

????????????????????????????????????????????????.? ??????????????????????????

?????????????????????????????????????????????????? ??????????????????????????

?????????????????????????????????????????????????? ??????????????????????????
?????????????????????????????????????????????????? ????????