specifying workbook for worksheet code name
Doug,
Here's one way, but there's no gain really. Better off just to use the Sheet
Name.
Sub test()
Dim strCodeName As String, strSheetName As String
strCodeName = "Sheet1"
With Workbooks("Book2")
strSheetName =
..VBProject.VBComponents(strCodeName).Properties(" Name").Value
MsgBox .Worksheets(strSheetName).Range("A1").Value
End With
End Sub
Rob
"Doug Glancy" wrote in message
...
My 3rd try on this question, hopefully the charm...
There was discussion last week of the advantage of referring to a
worksheet
by it's code name, e.g., Sheet1 rather than it's tab name, as in
WorkSheets("mysheet") or by index, as in WorkSheets(1). My question
is how to refer to a sheet in another workbook. I've tried:
Workbooks("mybook").Sheet1 and other such variations, without success.
I can access a sheet by code name in another workbook as a VBComponent
e.g.,
Workbooks("Book1").VBProject.VBComponents.Item("Sh eet1")
but then I can't, for example, refer to a range within the component.
Thanks in advance for any help a with this,
Doug
|