"Sheet" vs "Sheets"

[mikecox39]

I’m confused about bjects named: “sheet” and “sheets”.

I understand that Sheet refers to a specific worksheet but “sheets” can refer to either worksheets or chart sheets. How does XL know which object is being referenced if “sheets” can mean either. And why have an object named “Sheet” if Sheets covers both worksheet and chart sheet?

As I understand it Worsheets (1) refers to Sheet 1. I also understand that I could use Sheets (1); but that could mean I could be referring to 2 different object: worksheets and or sheets.

 

[Jonmo1]

Let me give it a shot..

All sheets (be they worksheets or charts) are indexed by VBA as they are shown left to right in the Excel Window
Even when a sheet is hidden, it retains the index it had before it was hidden.
Those index numbers will rearrange themselves as you rearrange the sheets visibly in the Excel Window.
It gets a bit tricky with hidden sheets, but they will retain the index # they had before they were hidden unless:
You move a sheet to the left of the sheet that is to the left of the hidden sheet.

There are different types of sheets (mainly Worksheets and ChartSheets)
Sheets is a collection of ALL sheets, regardless of the type
Worksheets is a collection of just the Worksheets (charts not included)
Charts is a collection of just the Chart sheets (worksheets not included)

Let's say you have 4 sheets (2 worksheets and 2 charts), and they appear in this order from left to right in the Excel Window

Worksheet1 | Chart1 | Worksheet2 | Chart2

Sheets(1) refers to the first sheet of ANY type, in this case Worksheet1
Sheets(2) refers to the 2nd sheet of ANY type, in this case Chart1
Sheets(3) refers to the 3rd sheet of ANY type, in this case Worksheet2
Sheets(4) refers to the 4th sheet of ANY type, in this case Chart2

Worksheets(1) refers to the first sheet of the Worksheet Type, in this case Worksheet1
Worksheets(2) refers to the 2nd sheet of the Worksheet Type, in this case WorksSheet2
Worksheets(3) would give a "Subscript Out Of Range" Error because there is not a 3rd sheet of the Worksheet type in the book.

Charts(1) refers to the first sheet of the Chart Type, in this case Chart1
Charts(2) refers to the 2nd sheet of the Chart Type, in this case Chart2
Charts(3) would give a "Subscript Out Of Range" Error because there is not a 3rd sheet of the Chart type in the book.


Hope that helps.