TIP: Worksheets vs. Sheets (w/Charts) Count

[EphesiansSix]

Hi All

I'm rather new to VBA, so this is probably obvious to you pros.
just spent several hours figuring it out the hard way though, an
thought it worth sharing.

-Sheets.Count- counts all sheets in the workbook, including chart
which were created as sheets. -Worksheets.Count- counts only dat
sheets (true "worksheets"), not charts which also happen to be sheets
Use accordingly

You can try the following code in a new blank worksheet for
demonstration. I recommend stepping through it (F8) to understan
exactly what's happening.

Sub proExcelSheetCounts(

'Message Boxes show the WORKSHEETS count and the SHEETS coun
MsgBox "Excel thinks there are " & Worksheets.Count & " Worksheets
& Chr(10) & "and " &
Sheets.Count & " total sheets.

'Create a chart as a new shee
Sheets(2).Selec
Range("A1:B10").Selec
Charts.Ad
ActiveChart.Location Where:=xlLocationAsNewSheet
Name:="ChartIMade

MsgBox "Excel thinks there are " & Worksheets.Count & " worksheets
& Chr(10) & "and " &
Sheets.Count & " total sheets.

'Add a new data shee
ActiveWorkbook.Sheets.Add After:=Worksheets(Worksheets.Count

MsgBox "Excel thinks there are " & Worksheets.Count & " Worksheets
& Chr(10) & "and " &
Sheets.Count & " total sheets.

'Actions on different counted sheet
Sheets(Worksheets.Count).Selec
Cells(1, 1).Value = "I'm on the last WORKSHEETS count page.
Sheets(Sheets.Count).Selec
Cells(1, 1).Value = "I'm on the last SHEETS count page.

End Su

Cheers, and God Bless

 

[Guest]

also try

charts.count

 

[EphesiansSix]

Brilliant, thanks Tom!