"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.

 

[pgc01]

Hi Mike

They are in fact, different objects.

Id you think of worksheet and sheet, Sheet is a higher level object (in terms of abstraction).

As you know a workbook has sheets.

There are 5 types of sheets, but some of them you don't work with, you will only find them in old workbooks.

The sheet types we use today are only 2:
- worksheets
- chart sheets

So, for ex.

- Activeworkbook.Sheets is a collection of all the sheets in the workbook, no matter what they specific type is.
In this case, for a current workbook, it's a collection with all the worksheets and chart sheets.

- Activeworkbook.Worksheets is a collection of the sheets in the workbook that have the type Worksheet

- Activeworkbook.Charts is a collection of the sheets in the workbook that have the type Chart Sheet

Does this help?

 

[shg]

Worksheets(1) is the first worksheet in a workbook from the left, even if preceded by one of more chart sheets.

Sheets(1) is the leftmost sheet in the workbook, regardless of type.

 

[mikecox39]

I appreciate your taking the time to address my question.

Id you think of worksheet and sheet, Sheet is a higher level object (in terms of abstraction).

Thanks for explaining that … but I didn’t mention worksheet in my question. My question had to do with the difference between “sheet” and “sheets”

There are 5 types of sheets, but some of them you don't work with, you will only find them in old workbooks.

The sheet types we use today are only 2:
- worksheets
- chart sheets

That information is helpful; but my confusion has to do with the use of “sheet” vs “sheets” in VBA code. Where “sheet” is specific to a particular worksheet and “Sheets” refers to either worksheets or Chart sheets.

- Activeworkbook.Sheets is a collection of all the sheets in the workbook, no matter what they specific type is.
In this case, for a current workbook, it's a collection with all the worksheets and chart sheets.

- Activeworkbook.Worksheets is a collection of the sheets in the workbook that have the type Worksheet

- Activeworkbook.Charts is a collection of the sheets in the workbook that have the type Chart Sheet

Yes, but if I write Sheets (1) and there is a worksheet 1 and a Chart Sheet 1 how does the program know which one I am referring to?

 

[shg]

If I write Sheets (1) and there is a worksheet 1 and a Chart Sheet 1 how does the program know which one I am referring to?

See post #3.

 

[Jonmo1]

Can you post example code displaying the 2 different references you're having trouble with?

I'm not aware of any VBA object that is just Sheet by itself.
VBA native Sheet objects are Sheets or Worksheets, And ActiveSheet

If you have code that references just Sheet (no s at the end), then it is likely a User Defined Variable previousely defined in that code.
Or perhaps it's the CodeName of a given Sheet.

 

[pgc01]

Remark: Read also post #3

That information is helpful; but my confusion has to do with the use of “sheet” vs “sheets” in VBA code. Where “sheet” is specific to a particular worksheet and “Sheets” refers to either worksheets or Chart sheets.

Not sure I understand.

Let's say you have 5 sheets in your active workbook

For ex.
Activeworkbook.Sheets(1) is a worksheet
Activeworkbook.Sheets(2) is a worksheet
Activeworkbook.Sheets(3) is a chart sheet
Activeworkbook.Sheets(4) is a worksheet
Activeworkbook.Sheets(5) is a chart sheet


I don't understand what you mean by "Where “sheet” is specific to a particular worksheet and “Sheets” refers to either worksheets or Chart sheets."

Sheets is a collection. Each sheet in the collection refers to a sheet that may be a worksheet or a chart sheet.

Please clarify.

 

[mikecox39]

Worksheets(1) is the first worksheet in a workbook from the left, even if preceded by one of more chart sheets.

Sheets(1) is the leftmost sheet in the workbook, regardless of type.

I must be missing something because both reply's referred to "Worksheets" and my question was about "Sheets" vs "Sheet".

Here's the statement that prompted this question:

You refer to the second worksheet in a workbook as Worksheets (2), and so on.

There is also a collection called Sheets, which is made up of all sheets in a workbook, whether they're worksheets or chart Sheets. If sheet1 is the first sheet in the workbook, you can reference it as follows: Sheets (1)

I'm just trying to understand how the program knows which type of object is being referenced with "sheets (1)"; if there is a Worksheets 1 and a Chart sheets 1

 

[pgc01]

P. S.

In my last example

Activeworkbook.Sheets(1) is Activeworkbook.Worksheets(1)
Activeworkbook.Sheets(2) is Activeworkbook.Worksheets(2)
Activeworkbook.Sheets(3) is Activeworkbook.Charts(1)
Activeworkbook.Sheets(4) is Activeworkbook.Worksheets(3)
Activeworkbook.Sheets(5) is Activeworkbook.Charts(2)

 

[mikecox39]

Can you post example code displaying the 2 different references you're having trouble with?

Here is the reading that prompted this question.

[/URL][/IMG]

I will study the other responses now, but Pixentral wasn't working when I posted my last response; but is now, so I'm posing it now in the hopes that it will clarify things because I don't know what I'm talking about so I'm having trouble making it clear why I'm confused.

I appreciate your effort to help me get this issue clear in my head.