Using Application.Goto in VBA to position a Worksheet

[Sparkle99]

Hi all,
I'm using this code to tidy-off my report worksheet at the end of some code...

Report1.Range("D5").Select
Application.Goto Report1.Range("D5"), Scroll:=True

This SHOULD position the report nicely as I have frozen the pane at D5.
And this code works in other reports.

So, why is my new report code just refusing to do either of the above statements ?
Have I missed something obvious ?
I've tried Report1.Select Report1.Activate - makes no difference.

Any suggestions greatfully received.

 

[pgc01]

How is Report1 declared and initialized?

If

Report1.Select

does not work, it seems Report1 does not refer to a sheet.

 

[Sparkle99]

Dim Report1 As Worksheet
Set Report1 = Application.ActiveWorkbook.Worksheets("Report")

Everything else for the building of the report is working fine. I'm setting the print area, auto-filters, colour-fill on columns, summary total rows.
It all looks lovely !!

It is just the code to scroll and select is being ignored.
When I use debug and step through, I can see the statement Report1.Range("D5").Select is just not doing anything.
The 'selected area' is the totals row (row 200ish) as this is the last row the code was processing.
So, I just want to position the report back to the top.
The Select and App.Goto statements are what I use successfully elsewhere.
So why are they not working here ??? (you can probably tell I'm a tad frustrated by all of this !)

 

[pgc01]

Hi

Did the statement that I posted work?

The Select and App.Goto statements are what I use successfully elsewhere.
So why are they not working here ??? (you can probably tell I'm a tad frustrated by all of this !)

I don't see how that is possible. Your statement

Report1.Range("D5").Select

has wrong syntax. It will not work unless Report1 is the active sheet.

The other one,

Application.Goto Report1.Range("D5"), Scroll:=True

should work.

In fact you don't need the first statement, the second one should be enough. Please try.

 

[Sparkle99]

Many thanks, pgc01.
My code was fine - sort of. What you did was make me re-visit all of my delcarations for the multiple report sheets I'm creating.
Then I realised I was referencing "Report1" instead of "Rpt1" - I have boolean variables, strings, integers that are "Report1..." and I never noticed the worksheet was "Rpt1".

Thanks again !!

 

[pgc01]

Hi

I'm glad your problem is solved.

Don't forget, however, to correct the statements type:

Rpt1.Range("D5").Select

As I said in my last post, this is wrong syntax. It will only work if at the moment of execution the active sheet is Rpt1.