Show a Cell at Top Left
 

Hi All,

I thought this will be easy but I am stuck.

I am generating a report in Excel 2003 and once the report is
generated, I want Cell Q1 to be on the top left of the visible screen
(basically I have some more reports left of Q1 which I dont want to be
visible after report is generated..I just want people to be looking at
this report which I generated now)

I was trying this:

Sheets("Sheet1").Select
Range("Q1").Select

It doesn't work.

Please help

Thanks,
Anshuman

Show a Cell at Top Left
 

This will just make Q1 the ActiveCell - try hiding the columns A to P
first.

Hope this helps.

Pete

On Jul 12, 1:55 pm, anshu wrote:
Hi All,

I thought this will be easy but I am stuck.

I am generating a report in Excel 2003 and once the report is
generated, I want Cell Q1 to be on the top left of the visible screen
(basically I have some more reports left of Q1 which I dont want to be
visible after report is generated..I just want people to be looking at
this report which I generated now)

I was trying this:

Sheets("Sheet1").Select
Range("Q1").Select

It doesn't work.

Please help

Thanks,
Anshuman

Show a Cell at Top Left
 

Hi Pete,

I thought about that but I also want people to have an option to look
at other reports which are there in columns A through P. At this
point, I just dont want other reports to stare at them when they
should be looking at the one I want them to show

Hope you understood where I am coming from.

Waiting for more answers...

Thanks,
Anshuman

On Jul 12, 8:59 pm, Pete_UK wrote:
This will just make Q1 the ActiveCell - try hiding the columns A to P
first.

Hope this helps.

Pete

On Jul 12, 1:55 pm, anshu wrote:

Hi All,

I thought this will be easy but I am stuck.

I am generating a report in Excel 2003 and once the report is
generated, I want Cell Q1 to be on the top left of the visible screen
(basically I have some more reports left of Q1 which I dont want to be
visible after report is generated..I just want people to be looking at
this report which I generated now)

I was trying this:

Sheets("Sheet1").Select
Range("Q1").Select

It doesn't work.

Please help

Thanks,
Anshuman

Show a Cell at Top Left
 

Try something like this:

'-----------Start of Code--------
Sub PutCellInUpperLeft(sCellRef As String)
Dim rCell As Range

Set rCell = Range(sCellRef)

With ActiveWindow
.ScrollColumn = rCell.Column
.ScrollRow = rCell.Row
End With
rCell.Select
End Sub

'Test that sub with this procedure
Sub TestCellScroll()
PutCellInUpperLeft sCellRef:="Q1"
End Sub
'-----------End of Code--------


Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"anshu" wrote:

Hi Pete,

I thought about that but I also want people to have an option to look
at other reports which are there in columns A through P. At this
point, I just dont want other reports to stare at them when they
should be looking at the one I want them to show

Hope you understood where I am coming from.

Waiting for more answers...

Thanks,
Anshuman

On Jul 12, 8:59 pm, Pete_UK wrote:
This will just make Q1 the ActiveCell - try hiding the columns A to P
first.

Hope this helps.

Pete

On Jul 12, 1:55 pm, anshu wrote:

Hi All,

I thought this will be easy but I am stuck.

I am generating a report in Excel 2003 and once the report is
generated, I want Cell Q1 to be on the top left of the visible screen
(basically I have some more reports left of Q1 which I dont want to be
visible after report is generated..I just want people to be looking at
this report which I generated now)

I was trying this:

Sheets("Sheet1").Select
Range("Q1").Select

It doesn't work.

Please help

Thanks,
Anshuman

Show a Cell at Top Left
 

try
Sub tst2()
Worksheets("Sheet1").Activate
Range("A1").Select
ActiveWindow.SmallScroll toright:=16
Range("Q1").Select

End Sub

"anshu" wrote:

Hi All,

I thought this will be easy but I am stuck.

I am generating a report in Excel 2003 and once the report is
generated, I want Cell Q1 to be on the top left of the visible screen
(basically I have some more reports left of Q1 which I dont want to be
visible after report is generated..I just want people to be looking at
this report which I generated now)

I was trying this:

Sheets("Sheet1").Select
Range("Q1").Select

It doesn't work.

Please help

Thanks,
Anshuman

Show a Cell at Top Left
 

Or more simply...............

Sub scrollto()
Sheets("Sheet1").Select
Application.Goto Reference:=Range("Q1"), Scroll:=True
End Sub


Gord Dibben MS Excel MVP

On Thu, 12 Jul 2007 08:06:37 -0700, bj wrote:

try
Sub tst2()
Worksheets("Sheet1").Activate
Range("A1").Select
ActiveWindow.SmallScroll toright:=16
Range("Q1").Select

End Sub

"anshu" wrote:

Hi All,

I thought this will be easy but I am stuck.

I am generating a report in Excel 2003 and once the report is
generated, I want Cell Q1 to be on the top left of the visible screen
(basically I have some more reports left of Q1 which I dont want to be
visible after report is generated..I just want people to be looking at
this report which I generated now)

I was trying this:

Sheets("Sheet1").Select
Range("Q1").Select

It doesn't work.

Please help

Thanks,
Anshuman

Show a Cell at Top Left
 

Thanks, Gord! I sprained my brain trying to remember that.

***********
Regards,
Ron

XL2002, WinXP


"Gord Dibben" wrote:

Or more simply...............

Sub scrollto()
Sheets("Sheet1").Select
Application.Goto Reference:=Range("Q1"), Scroll:=True
End Sub


Gord Dibben MS Excel MVP

On Thu, 12 Jul 2007 08:06:37 -0700, bj wrote:

try
Sub tst2()
Worksheets("Sheet1").Activate
Range("A1").Select
ActiveWindow.SmallScroll toright:=16
Range("Q1").Select

End Sub

"anshu" wrote:

Hi All,

I thought this will be easy but I am stuck.

I am generating a report in Excel 2003 and once the report is
generated, I want Cell Q1 to be on the top left of the visible screen
(basically I have some more reports left of Q1 which I dont want to be
visible after report is generated..I just want people to be looking at
this report which I generated now)

I was trying this:

Sheets("Sheet1").Select
Range("Q1").Select

It doesn't work.

Please help

Thanks,
Anshuman

Show a Cell at Top Left
 

You can get it down to one line if you name Sheet1!Q1

Application.Goto Reference:="MyName", Scroll:=True


Gord

On Thu, 12 Jul 2007 09:56:01 -0700, Ron Coderre
wrote:

Thanks, Gord! I sprained my brain trying to remember that.

***********
Regards,
Ron

XL2002, WinXP


"Gord Dibben" wrote:

Or more simply...............

Sub scrollto()
Sheets("Sheet1").Select
Application.Goto Reference:=Range("Q1"), Scroll:=True
End Sub


Gord Dibben MS Excel MVP

On Thu, 12 Jul 2007 08:06:37 -0700, bj wrote:

try
Sub tst2()
Worksheets("Sheet1").Activate
Range("A1").Select
ActiveWindow.SmallScroll toright:=16
Range("Q1").Select

End Sub

"anshu" wrote:

Hi All,

I thought this will be easy but I am stuck.

I am generating a report in Excel 2003 and once the report is
generated, I want Cell Q1 to be on the top left of the visible screen
(basically I have some more reports left of Q1 which I dont want to be
visible after report is generated..I just want people to be looking at
this report which I generated now)

I was trying this:

Sheets("Sheet1").Select
Range("Q1").Select

It doesn't work.

Please help

Thanks,
Anshuman

Show a Cell at Top Left
 

Thanks Everyone for the wonderful reply..

I am quite a novice with macros and I was tempted to try out the
simplest apparent solution, hence I tried Gord's solution:

Sub scrollto()
Sheets("Sheet1").Select
Application.Goto Reference:=Range("Q1"), Scroll:=True
End Sub

and it worked perfectly...

Thanks again.

Anshuman


On Jul 13, 1:46 am, Gord Dibben <gorddibbATshawDOTca wrote:
You can get it down to one line if you name Sheet1!Q1

Application.Goto Reference:="MyName", Scroll:=True

Gord

On Thu, 12 Jul 2007 09:56:01 -0700, Ron Coderre

wrote:
Thanks, Gord! I sprained my brain trying to remember that.

***********
Regards,
Ron

XL2002, WinXP

"Gord Dibben" wrote:

Or more simply...............

Sub scrollto()
Sheets("Sheet1").Select
Application.Goto Reference:=Range("Q1"), Scroll:=True
End Sub

Gord Dibben MS Excel MVP

On Thu, 12 Jul 2007 08:06:37 -0700, bj wrote:

try
Sub tst2()
Worksheets("Sheet1").Activate
Range("A1").Select
ActiveWindow.SmallScroll toright:=16
Range("Q1").Select

End Sub

"anshu" wrote:

Hi All,

I thought this will be easy but I am stuck.

I am generating a report in Excel 2003 and once the report is
generated, I want Cell Q1 to be on the top left of the visible screen
(basically I have some more reports left of Q1 which I dont want to be
visible after report is generated..I just want people to be looking at
this report which I generated now)

I was trying this:

Sheets("Sheet1").Select
Range("Q1").Select

It doesn't work.

Please help

Thanks,
Anshuman