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

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

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" <[email protected]> wrote in message
news:[email protected]...
> 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
>

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").Select
>
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
> >
>
>
>

Tom, are you saying that writing
Worksheets("PositionImports").Range("Positions").Select
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" <[email protected]> wrote in message
news:[email protected]...
> Worksheets("PositionImports").Select
> Worksheets("PositionImports").Range("Positions").Select
>
> 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" <[email protected]> wrote in
> message
> news:[email protected]...
>> 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
>>
>
>

Hi Bill

I believe waht Tom is saying is that:

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

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").Select
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > Worksheets("PositionImports").Select
> > Worksheets("PositionImports").Range("Positions").Select
> >
> > 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" <[email protected]> wrote in
> > message
> > news:[email protected]...
> >> 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
> >>
> >
> >
>
>
>

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" <wbenson1(SPAMSUCKS)@nycap.rr.com> wrote in message
news:[email protected]...
> Tom, are you saying that writing
> Worksheets("PositionImports").Range("Positions").Select
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > Worksheets("PositionImports").Select
> > Worksheets("PositionImports").Range("Positions").Select
> >
> > 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" <[email protected]> wrote in
> > message
> > news:[email protected]...
> >> 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
> >>
> >
> >
>
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <wbenson1(SPAMSUCKS)@nycap.rr.com> wrote in message
> news:[email protected]...
>> Tom, are you saying that writing
>> Worksheets("PositionImports").Range("Positions").Select
>> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > Worksheets("PositionImports").Select
>> > Worksheets("PositionImports").Range("Positions").Select
>> >
>> > 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" <[email protected]> wrote in
>> > message
>> > news:[email protected]...
>> >> 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
>> >>
>> >
>> >
>>
>>
>
>

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
news:[email protected]...
> Hi Bill
>
> I believe waht Tom is saying is that:
>
> Worksheets("PositionImports").Select
> Worksheets("PositionImports").Range("Positions").Select
>
> 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").Select
>> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > Worksheets("PositionImports").Select
>> > Worksheets("PositionImports").Range("Positions").Select
>> >
>> > 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" <[email protected]> wrote in
>> > message
>> > news:[email protected]...
>> >> 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
>> >>
>> >
>> >
>>
>>
>>