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

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

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

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

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

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