Macro - File Size of Individual Worksheets

[Excel-Access]

Hi

I have a workbook with about 15 worksheets that is about 15MB

Does anyone have a reliable VBA that would allow me to run and establish the size of each worksheet and this will be summarised on a new tab like

Tab 1 1MB
Tab 2 500k etc

I know it exists but the ones currently on the internet that I have tried does not work.

Regards

 

[Luke M]

Hi Sam,

It wouldn't get the report within the file, but this might be the quick and reliable.

Sub ParseSheets()
Dim ws As Worksheet
Dim fPath As String

'Where to save sheets
fPath = ThisWorkbook.Path

Application.ScreenUpdating = False
For Each ws In ThisWorkbook.Worksheets
    ws.Copy
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs fPath & "\COMPARE_" & ws.Name & ".xlsx"
    Application.DisplayAlerts = True
    ActiveWorkbook.Close
Next ws
Application.ScreenUpdating = True
MsgBox "Check out the COMPARE files in folder:" & vbNewLine & fPath
End Sub

Saves each worksheet as a separate file. Saved in same folder as original file, all files have the prefix "COMPARE_" in front of them to make it easy to compare in Windows explorer. Delete the files when done.

 

[Excel-Access]

Luke

This is great

Many thanks