跳到主要內容

如何檢查工作簿的每個工作表的大小?

假設您有一個包含多個工作表的大型工作簿,現在,您想找出每個工作表的大小,以確定需要減少的工作表。 有沒有快速的方法來處理此任務?

使用VBA代碼檢查每個工作表的大小

使用Kutools for Excel檢查每個工作表的大小


箭頭藍色右氣泡 使用VBA代碼檢查每個工作表的大小

使用以下VBA代碼,您可以快速獲取工作簿中每個工作表的大小。 請這樣做:

1。 按住 ALT + F11 鍵,然後打開 Microsoft Visual Basic for Applications窗口.

2。 點擊 插入 > 模塊,然後將以下代碼粘貼到 模塊窗口.

VBA代碼:檢查工作簿中每個工作表的大小

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. 然後按 F5 執行此代碼的鍵,以及名為 Kutoolsfor Excel 將插入到包含每個工作表名稱和文件大小的當前工作簿中,單位為“位”。 看截圖:

文檔檢查表大小 1


箭頭藍色右氣泡 使用Kutools for Excel檢查每個工作表的大小

如果你有 Excel的Kutools,其 拆分工作簿 實用程序,您可以將整個工作簿拆分為單獨的文件,然後轉到特定的文件夾以檢查每個文件的大小。

Excel的Kutools 包括300多個便捷的Excel工具。 30天免費試用,不受限制。 立即獲取.

安裝Kutools for Excel之後,請執行以下步驟:

1。 打開您要檢查其每個工作表的大小的工作簿,然後單擊 企業 > 工作簿工具 > 拆分工作簿,請參見屏幕截圖:

文檔檢查表大小 1

2。 在 拆分工作簿 對話框,檢查所有工作表,然後單擊 分裂 按鈕,然後指定一個文件夾來放置新的工作簿文件。 查看屏幕截圖:

文檔檢查表大小 3
-1
文檔檢查表大小 4

3。 然後,當前工作簿的每個工作表將另存為單獨的Excel文件,您可以轉到您的特定文件夾以檢查每個工作簿的大小。

文檔檢查表大小 1

要了解有關此拆分工作簿功能的更多信息。


相關文章:

如何在Excel中拆分工作簿以分隔Excel文件?

如何在Excel中將工作表和工作表導出並保存為新工作簿?

最佳辦公生產力工具

熱門特色: 尋找、突出顯示或識別重複項   |  刪除空白行   |  合併列或儲存格而不遺失數據   |   沒有公式的回合 ...
超級查詢: 多條件VLookup    多值VLookup  |   跨多個工作表的 VLookup   |   模糊查詢 ....
高級下拉列表: 快速建立下拉列表   |  依賴下拉列表   |  多選下拉列表 ....
欄目經理: 新增特定數量的列  |  移動列  |  切換隱藏列的可見性狀態  |  比較範圍和列 ...
特色功能: 網格焦點   |  設計圖   |   大方程式酒吧    工作簿和工作表管理器   |  資源庫 (自動文字)   |  日期選擇器   |  合併工作表   |  加密/解密單元格    按清單發送電子郵件   |  超級濾鏡   |   特殊過濾器 (過濾粗體/斜體/刪除線...)...
前 15 個工具集12 文本 工具 (添加文本, 刪除字符,...)   |   50+ 圖表 類型 (甘特圖,...)   |   40+ 實用 公式 (根據生日計算年齡,...)   |   19 插入 工具 (插入二維碼, 從路徑插入圖片,...)   |   12 轉化 工具 (數字到單詞, 貨幣兌換,...)   |   7 合併與拆分 工具 (高級合併行, 分裂細胞,...)   |   ... 和更多

使用 Kutools for Excel 增強您的 Excel 技能,體驗前所未有的效率。 Kutools for Excel 提供了 300 多種進階功能來提高生產力並節省時間。  點擊此處獲取您最需要的功能...

kte選項卡201905


Office選項卡為Office帶來了選項卡式界面,使您的工作更加輕鬆

  • 在Word,Excel,PowerPoint中啟用選項卡式編輯和閱讀,發布者,Access,Visio和Project。
  • 在同一窗口的新選項卡中而不是在新窗口中打開並創建多個文檔。
  • 將您的工作效率提高 50%,每天為您減少數百次鼠標點擊!
Comments (9)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Should probably add this between lines 9 and 10 in case some sheets are hidden to avoid the code crashing
For Each xWs In Sheets: xWs.Visible = True: Next
This comment was minimized by the moderator on the site
Thank you, very helpful,
I had a bunch of unnecessary formulas in a sheet and i just deleted that the file now works fine.
All these happen only because i could find the size of each sheet,
Thanks again.

Anson
This comment was minimized by the moderator on the site
Very VeryVeryVeryVery helpful.
Thank you!!
This comment was minimized by the moderator on the site
' Part 3 of 3 '--- paste break --- ' Format the output sheet Application.Sheets(xOutName).Activate Columns("B:B").Select Selection.NumberFormat = "#,##0_);(#,##0)" Columns("A:B").Select Columns("A:B").EntireColumn.AutoFit Range("A1").Select ' Even better, format it as a table. ActiveSheet.ListObjects.Add(xlSrcRange, Range("A1:B" & xIndex), , xlYes).Name = "WorksheetSizes" Application.ScreenUpdating = True Application.Application.DisplayAlerts = True Application.StatusBar = "" Application.Cursor = xlDefault Exit Sub ErrorHandler: MsgBox "Error #" & Err.Number & " - " & Err.Description & vbCrLf & "in procedure WorksheetSizes" End Sub
This comment was minimized by the moderator on the site
' Part 2 of 3 '--- paste break --- xWs.Visible = xlSheetVisible ' xOutFile = ThisWorkbook.Path & "\" & xWs.Name & ".xls" xWs.CopyQ 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 ' Repeat the above for chart sheets. For Each xWs In Application.ActiveWorkbook.Charts If xWs.Name xOutName Then Application.StatusBar = "Calculating Worksheet Sizes, Sheet " & xIndex & " of " & ActiveWorkbook.Worksheets.count - 1 & " - " & xWs.Name Debug.Print "Calculating Worksheet Sizes, Sheet " & xIndex & " of " & ActiveWorkbook.Worksheets.count - 1 & " - " & xWs.Name DoEvents ' include this so CTRL+Break can be detected. xWs.Visible = xlSheetVisible xOutFile = ThisWorkbook.Path & "\" & xWs.Name & ".xls" 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 '--- paste break ---
This comment was minimized by the moderator on the site
Here is a copy of the routine with a few enhancements I added. I had to break it into multiple posts due to the site limits. Public Sub WorksheetSizes() 'Update 20140526 ' https://www.extendoffice.com/documents/excel/1682-excel-check-size-of-each-sheet.html<br />' ' BS 4/4/2016: Modified to have a status bar and format the output. ' Fixed for hidden sheets that caused it to crash. ' Added support for Chart sheets Dim xWs As Object ' Worksheet or Chart Dim rng As Range Dim xOutWs As Worksheet Dim xOutFile As String Dim xOutName As String Dim xIndex As Long On Error GoTo ErrorHandler Application.Cursor = xlWait 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 Debug.Print ThisWorkbook.Path For Each xWs In Application.ActiveWorkbook.Worksheets If xWs.Name xOutName Then Application.StatusBar = "Calculating Worksheet Sizes, Sheet " & xIndex & " of " & ActiveWorkbook.Worksheets.count - 1 & " - " & xWs.Name Debug.Print "Calculating Worksheet Sizes, Sheet " & xIndex & " of " & ActiveWorkbook.Worksheets.count - 1 & " - " & xWs.Name DoEvents ' include this so CTRL+Break can be detected. '--- paste break ---
This comment was minimized by the moderator on the site
Hey Ben, Could you repaste the whole string of text with items #2 and #4 from your email added in? MY VBA knowledge is pretty limited and I'm not sure exactly where to add them into the For loop. My workbook has a number of hidden sheets and keeps crashing during the macro execution. Thanks, Bob
This comment was minimized by the moderator on the site
Thanks for providing the code snippet to the public. It's one of the better routines I found. Here are a few tweaks to it: 1) Add "Dim xIndex as Long" to the top if you're using Option Explicit. 2) Add this inside the For loop to handle hidden sheets (otherwise it crashes): xWs.Visible = xlSheetVisible 3) If you have full page "chart" sheets, you need to copy the code for the loop and iterate through the Application.ActiveWorkbook.Charts collection. If you do this, change the declaration of xWs from "Sheet" to "Object". 4) For a cheap status indicator (or for debugging issues) add this line inside the For loop: Debug.Print "Calculating Worksheet Sizes, Sheet " & xIndex & " of " & ActiveWorkbook.Worksheets.count - 1 & " - " & xWs.Name
This comment was minimized by the moderator on the site
Very helpful. Thank you!!
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations