Macro to determine size of each tab in workbook

**somesoldiers** · 12-23-2009, 10:12 AM

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

Please Login or Register  to view this content.

**Leith Ross** · 12-23-2009, 01:35 PM

Hello somesoldiers,

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

**somesoldiers** · 12-23-2009, 03:36 PM

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** · 12-23-2009, 06:21 PM

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.