Selecting
 

Hopefully someone will clarify an issue for me that has me baffled. This is
not the first time that selection of a range in another worksheet generates
errors. I want to select a range in another worksheet. Excel keeps giving
me a run time error '1004'.

These errors occur even if I fully qualify the name, such as
Worksheets("PI").Range("P").Select. I also recorded a macro to see what it
generated for code, then copied that and still received the error.

This is the code that I am using currently:

Worksheets("PositionImports").Select
Range("Positions").Select

This is the error with the Range select line highlighted in the IDE:

Run time error '1004'

Method 'Range' of object '_Worksheet' failed.


What gives here? Excel seems to not deal with this issue in a consistent
manner.

Thank you,
Mark

Selecting
 

Worksheets("PositionImports").Select
Worksheets("PositionImports").Range("Positions").S elect

Assumes Range("Positions") is on Sheet PositionImports.

I assume you are running this with a command button or in any event the code
is in a sheet module. In such cases, the unqualified Range("Positions") is
implicitly qualified by the sheet containing the code module. Since it is
not on that sheet, you get the error. Explicitly qualifying it removes that
problem.

--
Regards,
Tom Ogilvy



"Mark Petruszak" wrote in message
...
Hopefully someone will clarify an issue for me that has me baffled. This
is
not the first time that selection of a range in another worksheet
generates
errors. I want to select a range in another worksheet. Excel keeps
giving
me a run time error '1004'.

These errors occur even if I fully qualify the name, such as
Worksheets("PI").Range("P").Select. I also recorded a macro to see what
it
generated for code, then copied that and still received the error.

This is the code that I am using currently:

Worksheets("PositionImports").Select
Range("Positions").Select

This is the error with the Range select line highlighted in the IDE:

Run time error '1004'

Method 'Range' of object '_Worksheet' failed.


What gives here? Excel seems to not deal with this issue in a consistent
manner.

Thank you,
Mark

Selecting
 

Thank you Tom, for supplying the solution and the reasoning. I understand
the logic that you put forth but I don't like it. ;-)

Thank you for your help.

-Mark


"Tom Ogilvy" wrote:

Worksheets("PositionImports").Select
Worksheets("PositionImports").Range("Positions").S elect

Assumes Range("Positions") is on Sheet PositionImports.

I assume you are running this with a command button or in any event the code
is in a sheet module. In such cases, the unqualified Range("Positions") is
implicitly qualified by the sheet containing the code module. Since it is
not on that sheet, you get the error. Explicitly qualifying it removes that
problem.

--
Regards,
Tom Ogilvy



"Mark Petruszak" wrote in message
...
Hopefully someone will clarify an issue for me that has me baffled. This
is
not the first time that selection of a range in another worksheet
generates
errors. I want to select a range in another worksheet. Excel keeps
giving
me a run time error '1004'.

These errors occur even if I fully qualify the name, such as
Worksheets("PI").Range("P").Select. I also recorded a macro to see what
it
generated for code, then copied that and still received the error.

This is the code that I am using currently:

Worksheets("PositionImports").Select
Range("Positions").Select

This is the error with the Range select line highlighted in the IDE:

Run time error '1004'

Method 'Range' of object '_Worksheet' failed.


What gives here? Excel seems to not deal with this issue in a consistent
manner.

Thank you,
Mark

Selecting
 

Tom, are you saying that writing
Worksheets("PositionImports").Range("Positions").S elect
resolves the problem? I suppose this makes selecting the worksheet with
Worksheets("PositionImports").Select not of any (known) use in this context?

Bill




"Tom Ogilvy" wrote in message
...
Worksheets("PositionImports").Select
Worksheets("PositionImports").Range("Positions").S elect

Assumes Range("Positions") is on Sheet PositionImports.

I assume you are running this with a command button or in any event the
code
is in a sheet module. In such cases, the unqualified Range("Positions")
is
implicitly qualified by the sheet containing the code module. Since it is
not on that sheet, you get the error. Explicitly qualifying it removes
that
problem.

--
Regards,
Tom Ogilvy



"Mark Petruszak" wrote in
message
...
Hopefully someone will clarify an issue for me that has me baffled. This
is
not the first time that selection of a range in another worksheet
generates
errors. I want to select a range in another worksheet. Excel keeps
giving
me a run time error '1004'.

These errors occur even if I fully qualify the name, such as
Worksheets("PI").Range("P").Select. I also recorded a macro to see what
it
generated for code, then copied that and still received the error.

This is the code that I am using currently:

Worksheets("PositionImports").Select
Range("Positions").Select

This is the error with the Range select line highlighted in the IDE:

Run time error '1004'

Method 'Range' of object '_Worksheet' failed.


What gives here? Excel seems to not deal with this issue in a consistent
manner.

Thank you,
Mark

Selecting
 

Hi Bill

I believe waht Tom is saying is that:

Worksheets("PositionImports").Select
Worksheets("PositionImports").Range("Positions").S elect

solves the problem.

You can't select a range on a sheet which is not active hence you must first
select (or activate) the sheet. Because the code in question is in a sheet
module any unqualified range is assumed to be on the sheet containing the
code which is why you must qualify the range when selecting it.

I hope this makes sense
Rowan

"William Benson" wrote:

Tom, are you saying that writing
Worksheets("PositionImports").Range("Positions").S elect
resolves the problem? I suppose this makes selecting the worksheet with
Worksheets("PositionImports").Select not of any (known) use in this context?

Bill




"Tom Ogilvy" wrote in message
...
Worksheets("PositionImports").Select
Worksheets("PositionImports").Range("Positions").S elect

Assumes Range("Positions") is on Sheet PositionImports.

I assume you are running this with a command button or in any event the
code
is in a sheet module. In such cases, the unqualified Range("Positions")
is
implicitly qualified by the sheet containing the code module. Since it is
not on that sheet, you get the error. Explicitly qualifying it removes
that
problem.

--
Regards,
Tom Ogilvy



"Mark Petruszak" wrote in
message
...
Hopefully someone will clarify an issue for me that has me baffled. This
is
not the first time that selection of a range in another worksheet
generates
errors. I want to select a range in another worksheet. Excel keeps
giving
me a run time error '1004'.

These errors occur even if I fully qualify the name, such as
Worksheets("PI").Range("P").Select. I also recorded a macro to see what
it
generated for code, then copied that and still received the error.

This is the code that I am using currently:

Worksheets("PositionImports").Select
Range("Positions").Select

This is the error with the Range select line highlighted in the IDE:

Run time error '1004'

Method 'Range' of object '_Worksheet' failed.


What gives here? Excel seems to not deal with this issue in a consistent
manner.

Thank you,
Mark

Selecting
 

Both statements are required. The change in behavior i cited is more
restrictive, not less restrictive. Please re read the explanation.

--
Regards,
Tom Ogilvy

"William Benson" wrote in message
...
Tom, are you saying that writing
Worksheets("PositionImports").Range("Positions").S elect
resolves the problem? I suppose this makes selecting the worksheet with
Worksheets("PositionImports").Select not of any (known) use in this
context?

Bill




"Tom Ogilvy" wrote in message
...
Worksheets("PositionImports").Select
Worksheets("PositionImports").Range("Positions").S elect

Assumes Range("Positions") is on Sheet PositionImports.

I assume you are running this with a command button or in any event the
code
is in a sheet module. In such cases, the unqualified Range("Positions")
is
implicitly qualified by the sheet containing the code module. Since it
is
not on that sheet, you get the error. Explicitly qualifying it removes
that
problem.

--
Regards,
Tom Ogilvy



"Mark Petruszak" wrote in
message
...
Hopefully someone will clarify an issue for me that has me baffled.
This
is
not the first time that selection of a range in another worksheet
generates
errors. I want to select a range in another worksheet. Excel keeps
giving
me a run time error '1004'.

These errors occur even if I fully qualify the name, such as
Worksheets("PI").Range("P").Select. I also recorded a macro to see
what
it
generated for code, then copied that and still received the error.

This is the code that I am using currently:

Worksheets("PositionImports").Select
Range("Positions").Select

This is the error with the Range select line highlighted in the IDE:

Run time error '1004'

Method 'Range' of object '_Worksheet' failed.


What gives here? Excel seems to not deal with this issue in a
consistent
manner.

Thank you,
Mark

Selecting
 

Your explanation of previous (which I dutifully re-read) did no good to one
such as me, Tom, who did not know -- yes, after all these years, did not
know :-( -- that you cannot select a range on a non-active sheet.

Rowan's explanation did the trick.

Thanks all.

"Tom Ogilvy" wrote in message
...
Both statements are required. The change in behavior i cited is more
restrictive, not less restrictive. Please re read the explanation.

--
Regards,
Tom Ogilvy

"William Benson" wrote in message
...
Tom, are you saying that writing
Worksheets("PositionImports").Range("Positions").S elect
resolves the problem? I suppose this makes selecting the worksheet with
Worksheets("PositionImports").Select not of any (known) use in this
context?

Bill




"Tom Ogilvy" wrote in message
...
Worksheets("PositionImports").Select
Worksheets("PositionImports").Range("Positions").S elect

Assumes Range("Positions") is on Sheet PositionImports.

I assume you are running this with a command button or in any event the
code
is in a sheet module. In such cases, the unqualified
Range("Positions")
is
implicitly qualified by the sheet containing the code module. Since it
is
not on that sheet, you get the error. Explicitly qualifying it removes
that
problem.

--
Regards,
Tom Ogilvy



"Mark Petruszak" wrote in
message
...
Hopefully someone will clarify an issue for me that has me baffled.
This
is
not the first time that selection of a range in another worksheet
generates
errors. I want to select a range in another worksheet. Excel keeps
giving
me a run time error '1004'.

These errors occur even if I fully qualify the name, such as
Worksheets("PI").Range("P").Select. I also recorded a macro to see
what
it
generated for code, then copied that and still received the error.

This is the code that I am using currently:

Worksheets("PositionImports").Select
Range("Positions").Select

This is the error with the Range select line highlighted in the IDE:

Run time error '1004'

Method 'Range' of object '_Worksheet' failed.


What gives here? Excel seems to not deal with this issue in a
consistent
manner.

Thank you,
Mark

Selecting
 

It made sense after you explained to me that you cannot select a range on a
non-active sheet.

I have been learning to do nearly everything in the world to ranges without
selecting them first, therefore I forgot (or maybe never realized) this
fundamental fact.

Thanks Rowan!


"Rowan" <rowanzsa at hotmailNOSPAM dot com wrote in message
...
Hi Bill

I believe waht Tom is saying is that:

Worksheets("PositionImports").Select
Worksheets("PositionImports").Range("Positions").S elect

solves the problem.

You can't select a range on a sheet which is not active hence you must
first
select (or activate) the sheet. Because the code in question is in a sheet
module any unqualified range is assumed to be on the sheet containing the
code which is why you must qualify the range when selecting it.

I hope this makes sense
Rowan

"William Benson" wrote:

Tom, are you saying that writing
Worksheets("PositionImports").Range("Positions").S elect
resolves the problem? I suppose this makes selecting the worksheet with
Worksheets("PositionImports").Select not of any (known) use in this
context?

Bill




"Tom Ogilvy" wrote in message
...
Worksheets("PositionImports").Select
Worksheets("PositionImports").Range("Positions").S elect

Assumes Range("Positions") is on Sheet PositionImports.

I assume you are running this with a command button or in any event the
code
is in a sheet module. In such cases, the unqualified
Range("Positions")
is
implicitly qualified by the sheet containing the code module. Since it
is
not on that sheet, you get the error. Explicitly qualifying it removes
that
problem.

--
Regards,
Tom Ogilvy



"Mark Petruszak" wrote in
message
...
Hopefully someone will clarify an issue for me that has me baffled.
This
is
not the first time that selection of a range in another worksheet
generates
errors. I want to select a range in another worksheet. Excel keeps
giving
me a run time error '1004'.

These errors occur even if I fully qualify the name, such as
Worksheets("PI").Range("P").Select. I also recorded a macro to see
what
it
generated for code, then copied that and still received the error.

This is the code that I am using currently:

Worksheets("PositionImports").Select
Range("Positions").Select

This is the error with the Range select line highlighted in the IDE:

Run time error '1004'

Method 'Range' of object '_Worksheet' failed.


What gives here? Excel seems to not deal with this issue in a
consistent
manner.

Thank you,
Mark