How do you extract the VB name for a sheet in another file?

[Guest]

I know I can name sheets in the proptery window under the Name property.

So if i write a macro in Worbook1 refering to a sheet in Workbook1 that I
named "ThisfileSheet1VbNm I can say:

ThisfileSheet1VbNm.Range("A1").Select

But how do I refer to the name of a sheet in another workbook (workbook2),
within my code in workbook 1. If I have a sheet named "OtherfileSheet1VbNm"
in workbook 2, and I want to - upon opening workbook2 within macro in
workbook1- select this sheet the following code gives me an error: "Variable
not Defined"

OtherfileSheet1VbNm.Select

How do inform my code that this named sheet exists in the workbook I have
jsut opened?

Thanks

 

[Guest]

Usually the workbook you just opened will be active. In that case,

ActiveWorkbook.Sheets("OtherSheetName").Range("whatever").select

If you need to activate it first then:

Workbooks("Workbook2").Activate
ActiveWorkbook.Sheets("YourSheetName").Range("whatever").Select

HTH.

 

[Guest]

When I try to run the doe I get an error on the line:

TemplateVbNm.Select

This error occurs before the code even runs. You can see that I do attempt
to activate the file first but since the error ocurrs before the code runs,
the activate Method never gets a change to trigger. You can also see the
line of code above the failed line is commented out. This line has the
actuall sheet name in it. However I do not want to use this name as the user
may want to change it.

Windows(ExtractedFileName).Activate
'Worksheets("Template").Select
TemplateVbNm.Select

Thanks

 

[Tom Ogilvy]

One way:


Sub ABCD()
Dim bk As Workbook
Dim sh As Worksheet
Set bk = Workbooks.Open("C:\Data6\Workbook2.xls")
sName = bk.VBProject.VBComponents( _
"OtherfileSheet1VbNm").Properties("Name")
Set sh = bk.Worksheets(sName)
sh.Activate

End Sub

 

[Tim Williams]

did you try

Activeworkbook.TemplateVbNm.activate

Tim