VBA Code Activates a workbook only sometimes

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

GusEvans - 21 Mar 2006 02:14 GMT

Coded a relatively complex program. When I execute the following -
Workbooks("ACT-Reports.xls").Worksheets("Reports").Activate
It was working for a few weeks then the only way to do the same thing became -
For Each bkTempBooKxx In Workbooks
If bkTempBooKxx.Name = "ACT-Reports.xls" Then
bkTempBooKxx.Activate
For Each shtWork In Worksheets
If shtWork.Name = "Reports" Then
shtWork.Activate
Exit For
End If
Next
Exit For
End If
Next
Set bkACTRpt = ActiveWorkbook
Don't understand????

Signature

Thanks for any suggestions,
Gus Evans

Reply to this Message

Tom Ogilvy - 21 Mar 2006 03:40 GMT

You can't activate a sheet in a workbook which is not active. So you can go
either way

Workbooks("ACT-Reports.xls").Activate
Workbooks("ACT-Reports.xls").Worksheets("Reports").Activate

or

Application.Goto Workbooks("ACT-Reports.xls") _
.Worksheets("Reports").Range("A1")

But you don't need to loop to do it.

Signature

Regards,
Tom Ogilvy

> Coded a relatively complex program. When I execute the following -
> Workbooks("ACT-Reports.xls").Worksheets("Reports").Activate
[quoted text clipped - 13 lines]
> Set bkACTRpt = ActiveWorkbook
> Don't understand????

Reply to this Message

GusEvans - 21 Mar 2006 05:12 GMT

Signature

Gus Evans

> You can't activate a sheet in a workbook which is not active. So you can go
> either way
[quoted text clipped - 6 lines]
> Application.Goto Workbooks("ACT-Reports.xls") _
> .Worksheets("Reports").Range("A1")

The second command -
Workbooks("ACT-Reports.xls").Worksheets("Reports").Activate
and the -
Application.Goto Workbooks("ACT-Reports.xls") _
.Worksheets("Reports").Range("A1")
Both caused a 32809 error!

I can't figure it out either!

Gus Evans

Reply to this Message

GusEvans - 21 Mar 2006 05:27 GMT

Another comment -

I opened ACT-Reports.xls, then opened Sheet1.xls. went back to
ACT-Reports.xls, put the code in a module, then started stepping thru the
code, made Sheet1.xls the active workbook, and continued stepping thru the
code - got the 32809 error.

Gus Evans

Reply to this Message