also try

charts.count

--
Regards,
Tom Ogilvy


"EphesiansSix" wrote:


Hi All,

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

-Sheets.Count- counts all sheets in the workbook, including charts
which were created as sheets. -Worksheets.Count- counts only data
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 a
demonstration. I recommend stepping through it (F8) to understand
exactly what's happening.


Sub proExcelSheetCounts()

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

'Create a chart as a new sheet
Sheets(2).Select
Range("A1:B10").Select
Charts.Add
ActiveChart.Location Whe=xlLocationAsNewSheet,
Name:="ChartIMade"

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

'Add a new data sheet
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 sheets
Sheets(Worksheets.Count).Select
Cells(1, 1).Value = "I'm on the last WORKSHEETS count page."
Sheets(Sheets.Count).Select
Cells(1, 1).Value = "I'm on the last SHEETS count page."

End Sub

Cheers, and God Bless.


--
EphesiansSix
------------------------------------------------------------------------
EphesiansSix's Profile: http://www.excelforum.com/member.php...o&userid=35721
View this thread: http://www.excelforum.com/showthread...hreadid=560183