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

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

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

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

ActiveWorkbook.Sheets("OtherSheetName").Range("wha tever").select

If you need to activate it first then:

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

HTH.

"ExcelMonkey" wrote:

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

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

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

"quartz" wrote:

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

ActiveWorkbook.Sheets("OtherSheetName").Range("wha tever").select

If you need to activate it first then:

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

HTH.

"ExcelMonkey" wrote:

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

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

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


--
Regards,
Tom Ogilvy

"ExcelMonkey" wrote in message
...
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

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

did you try

Activeworkbook.TemplateVbNm.activate

Tim



"ExcelMonkey" wrote in message
...
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

"quartz" wrote:

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

ActiveWorkbook.Sheets("OtherSheetName").Range("wha tever").select

If you need to activate it first then:

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

HTH.

"ExcelMonkey" wrote:

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