יום שני, 2 במאי 2022

Three methods to find the number of sheets in a workbook

 

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 1: The SHEETS() Function

 

 

 

 

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

 

 


אין תגובות:

הוסף רשומת תגובה