Macro to determine size of each tab in workbook

[somesoldiers]

Hi Guys

I am using the below macro to determine the size of each tab in a workbook of 12.4mb, however my results are becoming skewed somewhere along the line ie when I add up all the individual file sizes produced on the RESULTS tab I am getting 14.4mb. Can anyone see anything in the macro that could be causing this and suggest how to amend please?

using 2003 for this one

thanks

Noel

Sub FindSheetSizes()
Dim Input_Workbook As Workbook
Dim Output_Workbook As Workbook
Dim ResultsSheet As Worksheet
 
Dim objSheet As Object
 
Dim StrPath As String
StrPath = "P:\File Size Check\"
 
Set objFSO = CreateObject("Scripting.FileSystemObject")
 
Application.ScreenUpdating = False
Set Input_Workbook = ActiveWorkbook
 
Application.DisplayAlerts = False
 
On Error Resume Next
Sheets("RESULTS").Delete
Sheets.Add(After:=Sheets(Sheets.Count)).Name = "RESULTS"
 
Application.DisplayAlerts = True
 
nRowCount = 0
 
For Each objSheet In Input_Workbook.Sheets
 
If objSheet.Name = "RESULTS" Then
 Application.ScreenUpdating = True
 Exit Sub
End If
 
objSheet.Copy
Set Output_Workbook = ActiveWorkbook
Output_Workbook.SaveAs StrPath & objSheet.Name & ".xls"
Output_Workbook.Close
 
Set objFile = objFSO.GetFile(StrPath & objSheet.Name & ".xls")
objSize = objFile.Size / 1024
objFile.Delete
 
nRowCount = nRowCount + 1
Sheets("RESULTS").Cells(nRowCount, 1).Value = objSheet.Name & "'s file size is " & objSize & " KB"
Next
 
Application.ScreenUpdating = True
  
End Sub

[Leith Ross]

Hello somesoldiers,

You are not summing the size of the individual worksheets, but individual workbooks that contain a single worksheet.

[somesoldiers]

Hi Leith

I see what you mean, my first thoughts were to split the tabs into seperate workbooks and then return the individual workbook sizes but the spike in sizes is outside any acceptable range, can you suggest how I might get around the splitting and just produce the RESULTS tab within the active workbook?

thanks a lot

Noel

[Leith Ross]

Hello somesoldiers,

Each worksheet is embedded within the Excel workbook partly as executable directives, partly as constants, variables, and other objects. Excel also pads the worksheet buffer area based on its best guess of your future needs. Given all this, there is no built in function nor a macro that could be created outside of Excel to accurately tell you how much space the worksheet occupies on the disk.

[somesoldiers]

Hi Leith

ok, I understand, thanks for taking the time to explain

Noel