Three methods to find the number of sheets in a workbook
Today, I’m going to demonstrate three ways to find the
number of sheets in a workbook. These sheets (or some of them) might be empty, so
Power Query won’t be able to supply us with an answer ☹
Please pay attention:
Since Methods 2&3 use Macros, the file must be saved as a “macro-enabled” workbook:
the file suffix (extension) must be: “.xlsm” and not “.xlsx”
Method 1: The SHEETS() Function
The sheets() function is a built-in function in Excel.
Method 2: VBA Macro
The VBA
Function returns the number of sheets in the workbook.
As can be seen, it can be referred to like any built-in function in Excel.
Method 2: VBA Macro
Method 3: Using 4.0 Macro
The GET.WORKBOOK 4.0 Macro Function returns information
about the current workbook. The argument 4 – returns the number of worksheets
in the workbook. Macro 4.0 functions cannot be defined as regular functions (in
a cell) but must be defined, as can be seen in the picture, as a named range.
Method 3: Using
4.0 Macro
אין תגובות:
הוסף רשומת תגובה