Name sheets in VB and Selecting them.
 

I am tyring to select a sheet in VB. I want to avoide using the sheet name
as the user may change this over time. I clicked onto the sheet in the VBA
Project Window and then went into the Name proptery in the Proptery window
and named it "VbImportSheet". When I use the followig line of code I get an
error:

Worksheets(VbImportSheet).Select

The error is a Run Time Error 9 Subscript out of range

What am I doing wrong?

Thanks

Name sheets in VB and Selecting them.
 

I put quotes in: Sheets("VbImportSheet").Select
and this did not work either.

Why?

"ExcelMonkey" wrote:



I am tyring to select a sheet in VB. I want to avoide using the sheet name
as the user may change this over time. I clicked onto the sheet in the VBA
Project Window and then went into the Name proptery in the Proptery window
and named it "VbImportSheet". When I use the followig line of code I get an
error:

Worksheets(VbImportSheet).Select

The error is a Run Time Error 9 Subscript out of range

What am I doing wrong?

Thanks

Name sheets in VB and Selecting them.
 

Hi ExcelMonkey,

Try:

VbImportSheet.Select

---
Regards,
Norman



"ExcelMonkey" wrote in message
...


I am tyring to select a sheet in VB. I want to avoide using the sheet
name
as the user may change this over time. I clicked onto the sheet in the
VBA
Project Window and then went into the Name proptery in the Proptery window
and named it "VbImportSheet". When I use the followig line of code I get
an
error:

Worksheets(VbImportSheet).Select

The error is a Run Time Error 9 Subscript out of range

What am I doing wrong?

Thanks

Name sheets in VB and Selecting them.
 

Hi Excel Monkey,

Try:

VbImportSheet.Select

---
Regards,
Norman



"ExcelMonkey" wrote in message
...
I put quotes in: Sheets("VbImportSheet").Select
and this did not work either.

Why?

"ExcelMonkey" wrote:



I am tyring to select a sheet in VB. I want to avoide using the sheet
name
as the user may change this over time. I clicked onto the sheet in the
VBA
Project Window and then went into the Name proptery in the Proptery
window
and named it "VbImportSheet". When I use the followig line of code I get
an
error:

Worksheets(VbImportSheet).Select

The error is a Run Time Error 9 Subscript out of range

What am I doing wrong?

Thanks

Name sheets in VB and Selecting them.
 

If you are using the codename of the sheet, you don't use the
Worksheets(). Excel has no knowledge of code name, only VBA does.
Therefore, you should use code like

VbImportSheet.Select


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"ExcelMonkey" wrote in
message
...


I am tyring to select a sheet in VB. I want to avoide using
the sheet name
as the user may change this over time. I clicked onto the
sheet in the VBA
Project Window and then went into the Name proptery in the
Proptery window
and named it "VbImportSheet". When I use the followig line of
code I get an
error:

Worksheets(VbImportSheet).Select

The error is a Run Time Error 9 Subscript out of range

What am I doing wrong?

Thanks