Set worksheet.
 

I have tried everything and the following statement always gives me an error:

Dim wks As Worksheet

Set wks = Sheets("Columns")

I have an .xls file that has some tabs in it for each "sheet". It turns out
that Sheet3 is named by the tab "Columns". I was expecting to be able to
access this sheet starting with the above statement. But the statement always
throws an error


Runtime error '1004':
Method 'Sheets' of object '_Global' failed

Why is it failing? How do I find out? Is this the correct syntax?

Thank you.

Kevin

Set worksheet.
 

If "Columns" was mis-spelled you would get a subscript out of range error.
Is the code in a module and not worksheet code?
--
Gary''s Student


"Kevin Burton" wrote:

I have tried everything and the following statement always gives me an error:

Dim wks As Worksheet

Set wks = Sheets("Columns")

I have an .xls file that has some tabs in it for each "sheet". It turns out
that Sheet3 is named by the tab "Columns". I was expecting to be able to
access this sheet starting with the above statement. But the statement always
throws an error


Runtime error '1004':
Method 'Sheets' of object '_Global' failed

Why is it failing? How do I find out? Is this the correct syntax?

Thank you.

Kevin

Set worksheet.
 

The only error that I am getting is the 1004 error. The dialog box doesn't
indicate anything about subscript out of range. It is almost like this is not
compiled in the right environment. How do I tell if the code is in a module
or worksheet? I click on 'Macros' and 'Create' then I paste in the code. Is
this worksheet code?

Thank you for your help.

"Gary''s Student" wrote:

If "Columns" was mis-spelled you would get a subscript out of range error.
Is the code in a module and not worksheet code?
--
Gary''s Student


"Kevin Burton" wrote:

I have tried everything and the following statement always gives me an error:

Dim wks As Worksheet

Set wks = Sheets("Columns")

I have an .xls file that has some tabs in it for each "sheet". It turns out
that Sheet3 is named by the tab "Columns". I was expecting to be able to
access this sheet starting with the above statement. But the statement always
throws an error


Runtime error '1004':
Method 'Sheets' of object '_Global' failed

Why is it failing? How do I find out? Is this the correct syntax?

Thank you.

Kevin

Set worksheet.
 

put this in a standard module, not a sheet module

Option Explicit
Dim wks As Worksheet
Sub w()
Set wks = Worksheets("Columns")
wks.Activate
Range("a5").Select
End Sub


--


Gary


"Kevin Burton" wrote in message
...
I have tried everything and the following statement always gives me an
error:

Dim wks As Worksheet

Set wks = Sheets("Columns")

I have an .xls file that has some tabs in it for each "sheet". It turns
out
that Sheet3 is named by the tab "Columns". I was expecting to be able to
access this sheet starting with the above statement. But the statement
always
throws an error


Runtime error '1004':
Method 'Sheets' of object '_Global' failed

Why is it failing? How do I find out? Is this the correct syntax?

Thank you.

Kevin

Set worksheet.
 

Sorry for my ignorance but what is a standard module and what is a sheet
module. I just clicked on 'Macros' then 'Create'

"Gary Keramidas" wrote:

put this in a standard module, not a sheet module

Option Explicit
Dim wks As Worksheet
Sub w()
Set wks = Worksheets("Columns")
wks.Activate
Range("a5").Select
End Sub


--


Gary


"Kevin Burton" wrote in message
...
I have tried everything and the following statement always gives me an
error:

Dim wks As Worksheet

Set wks = Sheets("Columns")

I have an .xls file that has some tabs in it for each "sheet". It turns
out
that Sheet3 is named by the tab "Columns". I was expecting to be able to
access this sheet starting with the above statement. But the statement
always
throws an error


Runtime error '1004':
Method 'Sheets' of object '_Global' failed

Why is it failing? How do I find out? Is this the correct syntax?

Thank you.

Kevin

Set worksheet.
 

that's a module. if you right click a sheetname and choose view code, that's
sheet code.

also, if you're in vb, you will see the sheets listed under vba project on
the left. if you have modules, they will be listed at the bottom of the
list, under modules.

--


Gary


"Kevin Burton" wrote in message
...
Sorry for my ignorance but what is a standard module and what is a sheet
module. I just clicked on 'Macros' then 'Create'

"Gary Keramidas" wrote:

put this in a standard module, not a sheet module

Option Explicit
Dim wks As Worksheet
Sub w()
Set wks = Worksheets("Columns")
wks.Activate
Range("a5").Select
End Sub


--


Gary


"Kevin Burton" wrote in message
...
I have tried everything and the following statement always gives me an
error:

Dim wks As Worksheet

Set wks = Sheets("Columns")

I have an .xls file that has some tabs in it for each "sheet". It turns
out
that Sheet3 is named by the tab "Columns". I was expecting to be able
to
access this sheet starting with the above statement. But the statement
always
throws an error


Runtime error '1004':
Method 'Sheets' of object '_Global' failed

Why is it failing? How do I find out? Is this the correct syntax?

Thank you.

Kevin