get the number of sheets in an active excel workbook 2

[hengsin]

hi, can i know how to get the number of sheets in an active excel workbook

 

[Bowers74]

Enter the following into a code module:

Sub SheetsNumber()
    msgbox ThisWorkbook.Worksheets.Count
End sub

I hope this helps!


Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[hengsin]

thanks, it's working. But when i modified your code before this:

Sub SheetsNumber()
msgbox ActiveWorkbook.Worksheets.Count
End sub

it's also working. I would like to know what are the different between ActiveWorkbook and ThisWorkbook.

 

[Bowers74]

Hi hegsin,

ActiveWorkbook refers to the workbook that is currently active (i.e. The workbook with the selected cells).

ThisWorkbook refers to the workbook in which the code that is currently running is located.

ThisWorkbook can also be (and in most cases is) the ActiveWorkbook.

If open and activate other workbooks using code from another workbook they become the ActiveWorkbook and the workbook that contains the code that opens and activates the other workbooks is always ThisWorkbook.

In your code, if you use ThisWorkbook you will only be able to count the worksheets in the workbook that is running that procedure. If you use ActiveWorkbook (as you have), then you will be able to count the worksheets in another workbook (if it is activated).

Depending on what you want to do, you can use either ThisWorkbook or ActiveWorkbook

I hope this helps!



Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[hengsin]

thanks again