How do I select a sheet
 

Hi all

I have a macro that uses this line of code. I recorded it and this is what
it gave me
Sheets(strDataName).Select
So i saved the sheet then went to lunch and came back to get this error
message.
"run-time error 1004 - select method of worksheet class failed"

Anyone know why this would work before but not after lunch?
Is there a different way to select a worksheet?

Thanks - Ben H.

How do I select a sheet
 

Check what strDataName is set to. There are many ways to select a sheet
1. Create a Sheet object and reference that
Dim nSheet as Worksheet
Set nSheet = Sheets("Sheet1")
nSheet.Activate
2. Activate By Name
Sheets("Sheets1").Activate
3. Activate By Index
Sheets(1).Activate
4.Declare String Variable, Activate by that (your current mode)
Dim strDataName as String
strDataName = "Sheet1"
Sheets(strDataName).Activate

there are probably others but that should get you close enough
HTH

Die_Another_Day
Ben H wrote:
Hi all

I have a macro that uses this line of code. I recorded it and this is what
it gave me
Sheets(strDataName).Select
So i saved the sheet then went to lunch and came back to get this error
message.
"run-time error 1004 - select method of worksheet class failed"

Anyone know why this would work before but not after lunch?
Is there a different way to select a worksheet?

Thanks - Ben H.

How do I select a sheet
 

You can not active (or select) a sheet that is not visible... Just in case
taht is your problem. Just a side note... It is rarely necessary to activate
a sheet. You can work with the cells on the sheet without the sheet being
active. There are quite a few benefits to doing it this way but you have to
get used to worksheet and range objects to do it well...
--
HTH...

Jim Thomlinson


"Die_Another_Day" wrote:

Check what strDataName is set to. There are many ways to select a sheet
1. Create a Sheet object and reference that
Dim nSheet as Worksheet
Set nSheet = Sheets("Sheet1")
nSheet.Activate
2. Activate By Name
Sheets("Sheets1").Activate
3. Activate By Index
Sheets(1).Activate
4.Declare String Variable, Activate by that (your current mode)
Dim strDataName as String
strDataName = "Sheet1"
Sheets(strDataName).Activate

there are probably others but that should get you close enough
HTH

Die_Another_Day
Ben H wrote:
Hi all

I have a macro that uses this line of code. I recorded it and this is what
it gave me
Sheets(strDataName).Select
So i saved the sheet then went to lunch and came back to get this error
message.
"run-time error 1004 - select method of worksheet class failed"

Anyone know why this would work before but not after lunch?
Is there a different way to select a worksheet?

Thanks - Ben H.

How do I select a sheet
 

The recorder didn't give you that code. strDataname is a variable, and the
recorder doesn't know about variables, it would give something like

Sheets("Sheet1").Activate

so someone has changed it.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Ben H" wrote in message
...
Hi all

I have a macro that uses this line of code. I recorded it and this is
what
it gave me
Sheets(strDataName).Select
So i saved the sheet then went to lunch and came back to get this error
message.
"run-time error 1004 - select method of worksheet class failed"

Anyone know why this would work before but not after lunch?
Is there a different way to select a worksheet?

Thanks - Ben H.