Size of each sheet

B

Brisbane Rob

Is there any way of telling the size of each sheet in a multisheet
workbook?

Thanks
 
J

Jim May

Not totally sure of what your asking for but I worked up this approach,
which Summaries to the First Position Worksheet named "Master" in
Cells A2:B4 (say)
all the worksheet names and the Usedrange address of each.

Post this code into a satndatd module:

Sub Foo()
Dim Cwsused As String
Dim i As Integer
For i = 2 To ThisWorkbook.Worksheets.Count
WsName = Worksheets(i).Name
Cwsused = Worksheets(i).UsedRange.Address(0, 0)
With Sheets("Master")
Cells(i, 1).Value = WsName
Cells(i, 2).Value = Cwsused
End With
Next i
End Sub


Post back to further define what you really want.

Jim May
 
D

Dave Peterson

You mean like cells used or how much harddisk space is used for each?

I'm guessing harddisk space.

I don't worry about it. Whatever is required for my workbook to work is what I
end up with.

But you could extract each worksheet to a new workbook, save that workbook and
look at the filesize of each of those workbooks.

There's a little overhead even if there's nothing in any of the worksheets, but
it should give you an estimate.
 
B

Brisbane Rob

The code for the range of each sheet is really useful and I'll be usin
it.Thanks. What I was really after is how much disk space each sheet i
a workbook is using. I've got a workbook with ten sheets (some wit
charts) and it weighs in at over 5 mb and I can't figure out where al
the disk space is being used. I need to thin it down so I can send i
out over the network and 5 mb is too big
 
Top