for some unknown reason, all of a sudden i can't make a macro
understand WHERE to perform tasks re: certain worksheets! things that
USED to work are now not working, such as

dim bldgs as worksheet
set bldgs = activeworkbook.worksheets(1)
bldgs.range("a4").select

it selects "a4" on whatever sheet is currently active.
the only way i can make things work is to use

bldgs.Activate

before i do anything. this is not what it used to do! did i perhaps
uncheck some option somewhere that somebody can think of?! i've looked
@ excel options & see nothing strange now (although for some reason
"manual" calculations was checked [& i didn't check it]... have fixed
that now).

if anybody has ideas, pls. let me know. i believe it is a
software/computer problem, not a macro problem.
thanks
susan

You must either FIRST select the sheet before selecting the range or use
application.goto sheets("yoursheet").range("yourrange")
--
Don Guillett
SalesAid Software
(E-Mail Removed)
"Susan" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> for some unknown reason, all of a sudden i can't make a macro
> understand WHERE to perform tasks re: certain worksheets! things that
> USED to work are now not working, such as
>
> dim bldgs as worksheet
> set bldgs = activeworkbook.worksheets(1)
> bldgs.range("a4").select
>
> it selects "a4" on whatever sheet is currently active.
> the only way i can make things work is to use
>
> bldgs.Activate
>
> before i do anything. this is not what it used to do! did i perhaps
> uncheck some option somewhere that somebody can think of?! i've looked
> @ excel options & see nothing strange now (although for some reason
> "manual" calculations was checked [& i didn't check it]... have fixed
> that now).
>
> if anybody has ideas, pls. let me know. i believe it is a
> software/computer problem, not a macro problem.
> thanks
> susan
>

A couple of things...

1. This line of code is mighty unpredicatable...
set bldgs = activeworkbook.worksheets(1)
Whatever might happend to be the first sheet in whatever might be the active
workbook at the time will be the object bldgs... This is open to problems if
you start woring with multiple workbooks of if you add or delete sheets from
the active workbook. A better line would be
set bldgs = thisworkbook.worksheets("MySpecialSheet")

2. In order to select a range on a sheet that sheet must first be the active
sheet so you want something more like

with bldgs
..select
..range("a4").select
end with

--
HTH...

Jim Thomlinson


"Susan" wrote:

> for some unknown reason, all of a sudden i can't make a macro
> understand WHERE to perform tasks re: certain worksheets! things that
> USED to work are now not working, such as
>
> dim bldgs as worksheet
> set bldgs = activeworkbook.worksheets(1)
> bldgs.range("a4").select
>
> it selects "a4" on whatever sheet is currently active.
> the only way i can make things work is to use
>
> bldgs.Activate
>
> before i do anything. this is not what it used to do! did i perhaps
> uncheck some option somewhere that somebody can think of?! i've looked
> @ excel options & see nothing strange now (although for some reason
> "manual" calculations was checked [& i didn't check it]... have fixed
> that now).
>
> if anybody has ideas, pls. let me know. i believe it is a
> software/computer problem, not a macro problem.
> thanks
> susan
>
>

thanks for the help, guys.
perhaps i misunderstood a concept....... i thought that once you
declared a variable & set it, you could do whatever you wanted with it
without selecting it, or the sheet it is on. yes, the general "A4"
obviously wouldn't work, but.................

dim rng1 as variant
dim bldgs as worksheet

set bldgs = thisworkbook.worksheets("MySpecialSheet")
set rng1 = bldgs.range("a5:c10")

rng1.select (copy, sort, print, etc.)

at this point, i was under the impression that you could do almost
anything with rng1 without selecting the specific worksheet first,
since the range had been declared & set. even if you wanted to enter
info into that range, it would put it in the correct place because it
was declared & set.

was i completely off base?
thanks for your kind explanations & assistance.
susan

Jim Thomlinson wrote:
> A couple of things...
>
> 1. This line of code is mighty unpredicatable...
> set bldgs = activeworkbook.worksheets(1)
> Whatever might happend to be the first sheet in whatever might be the active
> workbook at the time will be the object bldgs... This is open to problems if
> you start woring with multiple workbooks of if you add or delete sheets from
> the active workbook. A better line would be
> set bldgs = thisworkbook.worksheets("MySpecialSheet")
>
> 2. In order to select a range on a sheet that sheet must first be the active
> sheet so you want something more like
>
> with bldgs
> .select
> .range("a4").select
> end with
>
> --
> HTH...
>
> Jim Thomlinson
>
>
> "Susan" wrote:
>
> > for some unknown reason, all of a sudden i can't make a macro
> > understand WHERE to perform tasks re: certain worksheets! things that
> > USED to work are now not working, such as
> >
> > dim bldgs as worksheet
> > set bldgs = activeworkbook.worksheets(1)
> > bldgs.range("a4").select
> >
> > it selects "a4" on whatever sheet is currently active.
> > the only way i can make things work is to use
> >
> > bldgs.Activate
> >
> > before i do anything. this is not what it used to do! did i perhaps
> > uncheck some option somewhere that somebody can think of?! i've looked
> > @ excel options & see nothing strange now (although for some reason
> > "manual" calculations was checked [& i didn't check it]... have fixed
> > that now).
> >
> > if anybody has ideas, pls. let me know. i believe it is a
> > software/computer problem, not a macro problem.
> > thanks
> > susan
> >
> >

why not, as I suggested

set bldgs = thisworkbook.worksheets("MySpecialSheet")
application.goto = bldgs.range("a5:c10")


--
Don Guillett
SalesAid Software
(E-Mail Removed)
"Susan" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> thanks for the help, guys.
> perhaps i misunderstood a concept....... i thought that once you
> declared a variable & set it, you could do whatever you wanted with it
> without selecting it, or the sheet it is on. yes, the general "A4"
> obviously wouldn't work, but.................
>
> dim rng1 as variant
> dim bldgs as worksheet
>
> set bldgs = thisworkbook.worksheets("MySpecialSheet")
> set rng1 = bldgs.range("a5:c10")
>
> rng1.select (copy, sort, print, etc.)
>
> at this point, i was under the impression that you could do almost
> anything with rng1 without selecting the specific worksheet first,
> since the range had been declared & set. even if you wanted to enter
> info into that range, it would put it in the correct place because it
> was declared & set.
>
> was i completely off base?
> thanks for your kind explanations & assistance.
> susan
>
> Jim Thomlinson wrote:
>> A couple of things...
>>
>> 1. This line of code is mighty unpredicatable...
>> set bldgs = activeworkbook.worksheets(1)
>> Whatever might happend to be the first sheet in whatever might be the
>> active
>> workbook at the time will be the object bldgs... This is open to problems
>> if
>> you start woring with multiple workbooks of if you add or delete sheets
>> from
>> the active workbook. A better line would be
>> set bldgs = thisworkbook.worksheets("MySpecialSheet")
>>
>> 2. In order to select a range on a sheet that sheet must first be the
>> active
>> sheet so you want something more like
>>
>> with bldgs
>> .select
>> .range("a4").select
>> end with
>>
>> --
>> HTH...
>>
>> Jim Thomlinson
>>
>>
>> "Susan" wrote:
>>
>> > for some unknown reason, all of a sudden i can't make a macro
>> > understand WHERE to perform tasks re: certain worksheets! things that
>> > USED to work are now not working, such as
>> >
>> > dim bldgs as worksheet
>> > set bldgs = activeworkbook.worksheets(1)
>> > bldgs.range("a4").select
>> >
>> > it selects "a4" on whatever sheet is currently active.
>> > the only way i can make things work is to use
>> >
>> > bldgs.Activate
>> >
>> > before i do anything. this is not what it used to do! did i perhaps
>> > uncheck some option somewhere that somebody can think of?! i've looked
>> > @ excel options & see nothing strange now (although for some reason
>> > "manual" calculations was checked [& i didn't check it]... have fixed
>> > that now).
>> >
>> > if anybody has ideas, pls. let me know. i believe it is a
>> > software/computer problem, not a macro problem.
>> > thanks
>> > susan
>> >
>> >
>

yes, don, i can certainly do that. it just seems that it would make
for a lot of extra coding in a large macro.
but if that's what i have to do, then that's what i'll do. i guess i
just misunderstood the concept.

thank you!
susan

Don Guillett wrote:
> why not, as I suggested
>
> set bldgs = thisworkbook.worksheets("MySpecialSheet")
> application.goto = bldgs.range("a5:c10")
>
>
> --
> Don Guillett
> SalesAid Software
> (E-Mail Removed)
> "Susan" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > thanks for the help, guys.
> > perhaps i misunderstood a concept....... i thought that once you
> > declared a variable & set it, you could do whatever you wanted with it
> > without selecting it, or the sheet it is on. yes, the general "A4"
> > obviously wouldn't work, but.................
> >
> > dim rng1 as variant
> > dim bldgs as worksheet
> >
> > set bldgs = thisworkbook.worksheets("MySpecialSheet")
> > set rng1 = bldgs.range("a5:c10")
> >
> > rng1.select (copy, sort, print, etc.)
> >
> > at this point, i was under the impression that you could do almost
> > anything with rng1 without selecting the specific worksheet first,
> > since the range had been declared & set. even if you wanted to enter
> > info into that range, it would put it in the correct place because it
> > was declared & set.
> >
> > was i completely off base?
> > thanks for your kind explanations & assistance.
> > susan
> >
> > Jim Thomlinson wrote:
> >> A couple of things...
> >>
> >> 1. This line of code is mighty unpredicatable...
> >> set bldgs = activeworkbook.worksheets(1)
> >> Whatever might happend to be the first sheet in whatever might be the
> >> active
> >> workbook at the time will be the object bldgs... This is open to problems
> >> if
> >> you start woring with multiple workbooks of if you add or delete sheets
> >> from
> >> the active workbook. A better line would be
> >> set bldgs = thisworkbook.worksheets("MySpecialSheet")
> >>
> >> 2. In order to select a range on a sheet that sheet must first be the
> >> active
> >> sheet so you want something more like
> >>
> >> with bldgs
> >> .select
> >> .range("a4").select
> >> end with
> >>
> >> --
> >> HTH...
> >>
> >> Jim Thomlinson
> >>
> >>
> >> "Susan" wrote:
> >>
> >> > for some unknown reason, all of a sudden i can't make a macro
> >> > understand WHERE to perform tasks re: certain worksheets! things that
> >> > USED to work are now not working, such as
> >> >
> >> > dim bldgs as worksheet
> >> > set bldgs = activeworkbook.worksheets(1)
> >> > bldgs.range("a4").select
> >> >
> >> > it selects "a4" on whatever sheet is currently active.
> >> > the only way i can make things work is to use
> >> >
> >> > bldgs.Activate
> >> >
> >> > before i do anything. this is not what it used to do! did i perhaps
> >> > uncheck some option somewhere that somebody can think of?! i've looked
> >> > @ excel options & see nothing strange now (although for some reason
> >> > "manual" calculations was checked [& i didn't check it]... have fixed
> >> > that now).
> >> >
> >> > if anybody has ideas, pls. let me know. i believe it is a
> >> > software/computer problem, not a macro problem.
> >> > thanks
> >> > susan
> >> >
> >> >
> >

Susan,
It's quite possible you don't need to goto the sheet at all.ie:

bldgs.range("a5:c10").copy range("a4")

--
Don Guillett
SalesAid Software
(E-Mail Removed)
"Susan" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> yes, don, i can certainly do that. it just seems that it would make
> for a lot of extra coding in a large macro.
> but if that's what i have to do, then that's what i'll do. i guess i
> just misunderstood the concept.
>
> thank you!
> susan
>
> Don Guillett wrote:
>> why not, as I suggested
>>
>> set bldgs = thisworkbook.worksheets("MySpecialSheet")
>> application.goto = bldgs.range("a5:c10")
>>
>>
>> --
>> Don Guillett
>> SalesAid Software
>> (E-Mail Removed)
>> "Susan" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>> > thanks for the help, guys.
>> > perhaps i misunderstood a concept....... i thought that once you
>> > declared a variable & set it, you could do whatever you wanted with it
>> > without selecting it, or the sheet it is on. yes, the general "A4"
>> > obviously wouldn't work, but.................
>> >
>> > dim rng1 as variant
>> > dim bldgs as worksheet
>> >
>> > set bldgs = thisworkbook.worksheets("MySpecialSheet")
>> > set rng1 = bldgs.range("a5:c10")
>> >
>> > rng1.select (copy, sort, print, etc.)
>> >
>> > at this point, i was under the impression that you could do almost
>> > anything with rng1 without selecting the specific worksheet first,
>> > since the range had been declared & set. even if you wanted to enter
>> > info into that range, it would put it in the correct place because it
>> > was declared & set.
>> >
>> > was i completely off base?
>> > thanks for your kind explanations & assistance.
>> > susan
>> >
>> > Jim Thomlinson wrote:
>> >> A couple of things...
>> >>
>> >> 1. This line of code is mighty unpredicatable...
>> >> set bldgs = activeworkbook.worksheets(1)
>> >> Whatever might happend to be the first sheet in whatever might be the
>> >> active
>> >> workbook at the time will be the object bldgs... This is open to
>> >> problems
>> >> if
>> >> you start woring with multiple workbooks of if you add or delete
>> >> sheets
>> >> from
>> >> the active workbook. A better line would be
>> >> set bldgs = thisworkbook.worksheets("MySpecialSheet")
>> >>
>> >> 2. In order to select a range on a sheet that sheet must first be the
>> >> active
>> >> sheet so you want something more like
>> >>
>> >> with bldgs
>> >> .select
>> >> .range("a4").select
>> >> end with
>> >>
>> >> --
>> >> HTH...
>> >>
>> >> Jim Thomlinson
>> >>
>> >>
>> >> "Susan" wrote:
>> >>
>> >> > for some unknown reason, all of a sudden i can't make a macro
>> >> > understand WHERE to perform tasks re: certain worksheets! things
>> >> > that
>> >> > USED to work are now not working, such as
>> >> >
>> >> > dim bldgs as worksheet
>> >> > set bldgs = activeworkbook.worksheets(1)
>> >> > bldgs.range("a4").select
>> >> >
>> >> > it selects "a4" on whatever sheet is currently active.
>> >> > the only way i can make things work is to use
>> >> >
>> >> > bldgs.Activate
>> >> >
>> >> > before i do anything. this is not what it used to do! did i
>> >> > perhaps
>> >> > uncheck some option somewhere that somebody can think of?! i've
>> >> > looked
>> >> > @ excel options & see nothing strange now (although for some reason
>> >> > "manual" calculations was checked [& i didn't check it]... have
>> >> > fixed
>> >> > that now).
>> >> >
>> >> > if anybody has ideas, pls. let me know. i believe it is a
>> >> > software/computer problem, not a macro problem.
>> >> > thanks
>> >> > susan
>> >> >
>> >> >
>> >
>