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 Whe=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

--
EphesiansSi
-----------------------------------------------------------------------
EphesiansSix's Profile: http://www.excelforum.com/member.php...fo&userid=3572
View this thread: http://www.excelforum.com/showthread.php?threadid=56018

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

Brilliant, thanks Tom!


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