simple code not working

  • Thread starter Thread starter Susan
  • Start date Start date
S

Susan

this code bit has been working forever. recently had a virus & had to
reinstall microsoft office 07. now it won't work. i'm assuming
there's some wording difference it's looking for - can anybody help me
out?
thanks.
susan
++++++++++++++++++++++++++++++++++++++
ThisWorkbook.Worksheets("Indirect Costs").Select

With Worksheets("Indirect Costs")
.Range("a1").Select
End With
++++++++++++++++++++++++++++++++++++++
i also tried the following which stops on "Activate":

With ThisWorkbook
With Worksheets("Indirect Costs")
.Activate
.Range("a1").Select
End With
End With
++++++++++++++++++++++++++++++++++++++
 
Add a dot

With ThisWorkbook
With .Worksheets("Indirect Costs")
.Activate
.Range("A1").Select
End With
End With
 
I'd use a variation of Don's code:

sub gothere()
application.goto Thisworkbook.worksheets("Indirect Costs").Range("a1")
end sub

And this variation of Bob's code:

With ThisWorkbook
.Activate
With .Worksheets("Indirect Costs")
.Activate
.Range("a1").Select
End With
End With

I was kind of surprised that Bob's code (without the workbook activate
statement) worked if ThisWorkbook wasn't the activeworkbook.
 
Bob, it's all community code and that makes it yours!

I've always activated the workbook before selecting/activating a sheet in that
workbook. I wonder if that's required in all versions--or just a requirement of
the version that I first learned excel (xl95/xl97???).
 
this code bit has been working forever. recently had a virus& had to
reinstall microsoft office 07. now it won't work. i'm assuming
there's some wording difference it's looking for - can anybody help me
out?
thanks.
susan
++++++++++++++++++++++++++++++++++++++
ThisWorkbook.Worksheets("Indirect Costs").Select

With Worksheets("Indirect Costs")
.Range("a1").Select
End With
++++++++++++++++++++++++++++++++++++++
i also tried the following which stops on "Activate":

With ThisWorkbook
With Worksheets("Indirect Costs")
.Activate
.Range("a1").Select
End With
End With
++++++++++++++++++++++++++++++++++++++
Click to expand...

Just another way.
This requires you to add ' around the tab name because the name has a
space in it

Application.Goto Range("'Indirect Costs'!A1")

= = = = = = =
HTH :>)
Dana DeLouis
 
I wouldn't use this syntax.

Application.Goto Range("'Indirect Costs'!A1")

Depending on where the code is located (like a sheet module), it may not work.
 
i tried bob's first because it was closest to my own, but it still
breaks on "activate" with "application-defined or object-defined
error".

then i tried dave's variation on bob's code, but it still breaks on
the 2nd "activate" with the same error. i have stared at the coding &
the name of the page till i am blue in the face & i am sure they are
identical.

then i tried the application.goto codes & they also failed with the
same error. the name of the sheet has NOT changed. the original
coding did NOT change. just one day i opened it up & it didn't work,
& now it still won't. any way to verify the sheet name in the
immediate window?

thanks for all your help
susan
 
more info. i looked in the project properties & found that the
"Indirect Costs" sheet was sheet 4. so i changed the application.goto
coding to Worksheets(4).range("a1").select. and it selected the wrong
sheet. so in the immediate window i typed ?
ThisWorkbook.Worksheets(4).name & got a different name than the one i
expected. tried it with several other of the worksheets & got
additionally wrong information.

i think my virus is not gone after all.

susan
 
Application.Goto ThisWorkbook.Worksheets(Sheet4).Range("a1")

Sheet4 = ("Indirect Costs") in the properties window where it says
"Microsoft Excel Objects".
get same error.

ThisWorkbook.Activate
With Sheet4
.Activate
.Range("A1").Select
End With

errors out on "With Sheet4". worksheet is visible. this error is
occurring on all worksheets even back 2 years. it is the same
worksheet that gets saved month-to-month. code has not changed.
sheet name has not changed. i'm perplexed.
susan
 
If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results.


--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
Application.Goto ThisWorkbook.Worksheets(Sheet4).Range("a1")

Sheet4 = ("Indirect Costs") in the properties window where it says
"Microsoft Excel Objects".
get same error.

ThisWorkbook.Activate
With Sheet4
.Activate
.Range("A1").Select
End With

errors out on "With Sheet4". worksheet is visible. this error is
occurring on all worksheets even back 2 years. it is the same
worksheet that gets saved month-to-month. code has not changed.
sheet name has not changed. i'm perplexed.
susan
 
Application.Goto Sheet4.Range("a1")


Application.Goto ThisWorkbook.Worksheets(Sheet4).Range("a1")

Sheet4 = ("Indirect Costs") in the properties window where it says
"Microsoft Excel Objects".
get same error.

ThisWorkbook.Activate
With Sheet4
.Activate
.Range("A1").Select
End With

errors out on "With Sheet4". worksheet is visible. this error is
occurring on all worksheets even back 2 years. it is the same
worksheet that gets saved month-to-month. code has not changed.
sheet name has not changed. i'm perplexed.
susan
Click to expand...
 
ps. If using the codename works, then I'd bet big money that the worksheet name
(that the user sees on the tab) was changed.

Maybe an extra space (leading/trailing/embedded????) or even a typo.
 
sigh. nope, nothing works. i even rearranged how the macro works
(because i wrote it 2 years ago as a beginner) & it still doesn't
work. i think i'm going to have to send it to don. i don't have code
cleaner on my computer anymore since the virus but i can get it again.
i appreciate the help from everybody!
:)
susan
 
On 31/03/2010 5:17 AM, Susan wrote:

Could it be as simple as

Sheets("Indirect Costs").Select

Range("A1").Select
 
If it has worked previously then it was pure chance that ThisWorkbook was the
ActiveWorkbook at the time the code was executed. You cannot Activate a
Workbook and Select a Worksheet in the one line. Need to first Activate the
Workbook and then Select the worksheet.

ThisWorkbook.Activate

Worksheets("Indirect Costs").Select

With Worksheets("Indirect Costs")
.Range("a1").Select
End With
 
ossie - yes, it generally was the only workbook open & active at the
time of it executing, as it is now.

update:
our computer tech examined the coding & found everything was ok. he
made a copy of the sheet, deleted the original sheet, and renamed the
sheet to it's original name. now it works fine. don't know why!
thanks again
susan
 
Back
Top