Supposing you have a large workbook which contains multiple worksheets, and now, you want to find out the size of each worksheet to determine which sheet need to be reduced. Are there any quick methods to deal with this task?
Check the size of each worksheet with VBA code
Check the size of each worksheet with Kutools for Excel
Check the size of each worksheet with VBA code
With the following VBA code, you can quickly get the size of each worksheet in your workbook. Please do as this:
1. Hold down the ALT + F11 keys, and it opens the Microsoft Visual Basic for Applications window.
2. Click Insert > Module, and paste the following code in the Module Window.
VBA code: Check the size of each worksheet in a workbook< /p>
Sub WorksheetSizes()
'Update 20140526
Dim xWs As Worksheet
Dim Rng As Range
Dim xOutWs As Worksheet
Dim xOutFile As String
Dim xOutName As String
xOutName = "KutoolsforExcel"
xOutFile = ThisWorkbook.Path & "\TempWb.xls"
On Error Resume Next
Application.DisplayAlerts = False
Err = 0
Set xOutWs = Application.Worksheets(xOutName)
If Err = 0 Then
xOutWs.Delete
Err = 0
End If
With Application.ActiveWorkbook.Worksheets.Add(Before:=Application.Worksheets(1))
.Name = xOutName
.Range("A1").Resize(1, 2).Value = Array("Worksheet Name", "Size")
End With
Set xOutWs = Application.Worksheets(xOutName)
Application.ScreenUpdating = False
xIndex = 1
For Each xWs In Application.ActiveWorkbook.Worksheets
If xWs.Name <> xOutName Then
xWs.Copy
Application.ActiveWorkbook.SaveAs xOutFile
Application.ActiveWorkbook.Close SaveChanges:=False
Set Rng = xOutWs.Range("A1").Offset(xIndex, 0)
Rng.Resize(1, 2).Value = Array(xWs.Name, VBA.FileLen(xOutFile))
Kill xOutFile
xIndex = xIndex + 1
End If
Next
Application.ScreenUpdating = True
Application.Application.DisplayAlerts = True
End Sub
3. Then press F5 key to execute this code, and a new worksheet named KutoolsforExcel will be inserted into the current workbook which contains each worksheet name and file size, and the unit is Bit. See screenshot:
Check the size of each worksheet with Kutools for ExcelIf you have Kutools for Excel, with its Split Workbook utility, you can split the whole workbook into separate files, and then go to the specific folder to check the size of each file.
Kutools for Excel includes more than 300 handy Excel tools. Free to try with no limitation in 30 days. Get it Now.
After installing Kutools for Excel, do with following steps:
1. Open the workbook you want to check the size of its each worksheet, and click Enterprise > Workbook Tools > Split Workbook, see screenshot:
2. In the Split Workbook dialog, check all the worksheets and click Split button, and then specify a folder to put the new workbook files. See screenshots:
 |
 |
 |
3. And then each worksheet of your current workbook will be saved as separated Excel file, you can go to your specific folder to check the size of each workbook.
To know more about this Split Workbook feature.
Related articles:
How to split a workbook to separate Excel files in Excel?
How to export and save sheets and worksheets as new workbook in Excel?
