There was discussion last week of the advantage of referring to a worksheet
by Sheet1 rather than WorkSheets("mysheet") or WorkSheets(1). My question
is how to specify what workbook the sheet is in. Nothing I've tried works.
For example I've tried:

Workbooks("mybook").Sheet1

Thanks in advance,

Doug

Hi Doug,

Since you can call worksheets only from a collection (not directly wit
their names), you need to use an object variable or type th
collection

Try this :

Dim xsThisWorksheet as Worksheet

Set xsThisWorksheet = Workbooks(WorkbookIndex).Worksheets(SheetIndex)

you can have the same result by using workbookname and sheetname. Bu
don't forget to put them into ", if you use names

--
Message posted from http://www.ExcelForum.com

Thanks Toltag, but that's not what I meant. I'm talking about the ability
to refer to as simply Sheet1 as in:

Sheet1.select

If you open a new workbook and look in the project explorer under Microsoft
Excel Object you'll see something like:

Sheet1 (Sheet1)

and you can refer to a sheet as simply Sheet1 without quotes or using the
collection syntax, e.g.:

Sheet1.Select

However, I can't figure out how to use this syntax while referring to
workbooks other than the active workbook.

Doug

"tolgag " wrote in message
...
Hi Doug,

Since you can call worksheets only from a collection (not directly with
their names), you need to use an object variable or type the
collection

Try this :

Dim xsThisWorksheet as Worksheet

Set xsThisWorksheet = Workbooks(WorkbookIndex).Worksheets(SheetIndex)

you can have the same result by using workbookname and sheetname. But
don't forget to put them into ", if you use names.


---
Message posted from http://www.ExcelForum.com/