specifying workbook for Sheet1

  • Thread starter Thread starter Doug Glancy
  • Start date Start date
D

Doug Glancy

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
 
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
 
Back
Top