Перейти до основного матеріалу

Як перевірити розмір кожного аркуша книги?

Припустимо, у вас є велика книга, яка містить кілька аркушів, і тепер ви хочете дізнатись розмір кожного аркуша, щоб визначити, який аркуш потрібно зменшити. Чи існують швидкі методи вирішення цього завдання?

Перевірте розмір кожного аркуша за допомогою коду VBA

Перевірте розмір кожного аркуша за допомогою Kutools для Excel


стрілка синя права міхур Перевірте розмір кожного аркуша за допомогою коду VBA

За допомогою наведеного нижче коду VBA ви можете швидко отримати розмір кожного аркуша у вашій книзі. Будь ласка, зробіть так:

1. Утримуйте клавішу ALT + F11 і відкриває Вікно Microsoft Visual Basic для програм.

2. Клацання Insert > Модуліта вставте наступний код у Вікно модуля.

Код VBA: Перевірте розмір кожного аркуша в книзі </ 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. Потім натисніть F5 ключ для виконання цього коду та новий аркуш із назвою Kutools для Excel буде вставлено в поточну книгу, яка містить ім’я кожного робочого аркуша та розмір файлу, а одиницею є біт. Дивіться знімок екрана:

doc-check-sheet-size1


стрілка синя права міхур Перевірте розмір кожного аркуша за допомогою Kutools для Excel

Якщо у вас є Kutools для Excel, З його Роздільна робоча книга Ви можете розділити всю книгу на окремі файли, а потім перейти до певної папки, щоб перевірити розмір кожного файлу.

Kutools для Excel включає більше 300 зручних інструментів Excel. Безкоштовно спробувати без обмежень протягом 30 днів. Отримати зараз.

Після встановлення Kutools для Excel виконайте такі дії:

1. Відкрийте книгу, для якої потрібно перевірити розмір кожного її аркуша, та натисніть підприємство > Інструменти робочої книги > Роздільна робоча книга, див. скріншот:

doc-check-sheet-size1

2, в Роздільна робоча книга діалогове вікно, перевірте всі аркуші та натисніть розщеплений , а потім вкажіть папку, куди слід додати нові файли книги. Дивіться скріншоти:

doc-check-sheet-size3
-1
doc-check-sheet-size4

3. І тоді кожен аркуш вашої поточної книги буде збережений як окремий файл Excel, ви можете перейти до вашої конкретної папки, щоб перевірити розмір кожної книги.

doc-check-sheet-size1

Щоб дізнатись більше про цю функцію розділеної робочої книги.


Статті по темі:

Як розділити книгу на окремі файли Excel у програмі Excel?

Як експортувати та зберегти аркуші та аркуші як нову книгу в Excel?

Найкращі інструменти продуктивності офісу

Популярні функції: Знайдіть, виділіть або визначте дублікати   |  Видалити порожні рядки   |  Об’єднайте стовпці або клітинки без втрати даних   |   Раунд без Формули ...
Супер пошук: VLookup за кількома критеріями    Багатозначний VLookup  |   VLookup на кількох аркушах   |   Нечіткий пошук ....
Розширений розкривний список: Швидке створення випадаючого списку   |  Залежний спадний список   |  Виберіть розкривний список, що вибирається ....
Менеджер колонок: Додайте конкретну кількість стовпців  |  Перемістити стовпці  |  Перемкнути статус видимості прихованих стовпців  |  Порівняйте діапазони та стовпці ...
Особливості: Фокус сітки   |  Перегляд дизайну   |   Велика панель формул    Диспетчер робочих книг і аркушів   |  Бібліотека ресурсів (автотекст)   |  Вибір дати   |  Об’єднайте робочі аркуші   |  Шифрування/розшифрування клітинок    Надсилайте листи за списком   |  Супер фільтр   |   Спеціальний фільтр (фільтр жирний/курсив/закреслений...) ...
Топ-15 наборів інструментів12 текст Tools (додати текст, Видалити символи, ...)   |   50 + Графік типи (діаграма Ганта, ...)   |   40+ Практичний Формули (Розрахуйте вік на основі дня народження, ...)   |   19 вставка Tools (Вставте QR-код, Вставити зображення зі шляху, ...)   |   12 Перетворення Tools (Числа до слів, Валютна конверсія, ...)   |   7 Злиття та розділення Tools (Розширені комбіновані ряди, Розділені клітини, ...)   |   ... і більше

Покращуйте свої навички Excel за допомогою Kutools для Excel і відчуйте ефективність, як ніколи раніше. Kutools для Excel пропонує понад 300 додаткових функцій для підвищення продуктивності та економії часу.  Натисніть тут, щоб отримати функцію, яка вам найбільше потрібна...

вкладка kte 201905


Вкладка Office Передає інтерфейс із вкладками в Office і значно полегшує вашу роботу

  • Увімкніть редагування та читання на вкладках у Word, Excel, PowerPoint, Publisher, 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