I have crudely written a macro that takes the cursor to A1 on all worksheets
in a workbook. I'm hoping to add a command that will send the curso to the
upper left of any frozen cells first, then go to A1 (basically I want it to
perform Ctrl-Home first). Does anyone have any advice on this? Below is the
code I am currently using.

Dim wk As Worksheet
For Each wk In ActiveWorkbook.Worksheets
wk.Activate
Range("A1").Select
Next wk

Sheets(1).Activate

Thanks for the help!

Hi,

Look at the sendkeys command, here is some code:

SendKeys "^{Home}"

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Mike" wrote:

> I have crudely written a macro that takes the cursor to A1 on all worksheets
> in a workbook. I'm hoping to add a command that will send the curso to the
> upper left of any frozen cells first, then go to A1 (basically I want it to
> perform Ctrl-Home first). Does anyone have any advice on this? Below is the
> code I am currently using.
>
> Dim wk As Worksheet
> For Each wk In ActiveWorkbook.Worksheets
> wk.Activate
> Range("A1").Select
> Next wk
>
> Sheets(1).Activate
>
> Thanks for the help!

Application.ActiveWindow.ScrollRow
and
Application.ActiveWindow.ScrollColumn
will give you the row and column numbers which you can use in your code...



"Shane Devenshire" wrote:

> Hi,
>
> Look at the sendkeys command, here is some code:
>
> SendKeys "^{Home}"
>
> --
> If this helps, please click the Yes button.
>
> Cheers,
> Shane Devenshire
>
>
> "Mike" wrote:
>
> > I have crudely written a macro that takes the cursor to A1 on all worksheets
> > in a workbook. I'm hoping to add a command that will send the curso to the
> > upper left of any frozen cells first, then go to A1 (basically I want it to
> > perform Ctrl-Home first). Does anyone have any advice on this? Below is the
> > code I am currently using.
> >
> > Dim wk As Worksheet
> > For Each wk In ActiveWorkbook.Worksheets
> > wk.Activate
> > Range("A1").Select
> > Next wk
> >
> > Sheets(1).Activate
> >
> > Thanks for the help!

SendKeys isn't working for me.. Sheeloo, can you give me an example of how
to use those commands in my code?

"Sheeloo" wrote:

> Application.ActiveWindow.ScrollRow
> and
> Application.ActiveWindow.ScrollColumn
> will give you the row and column numbers which you can use in your code...
>
>
>
> "Shane Devenshire" wrote:
>
> > Hi,
> >
> > Look at the sendkeys command, here is some code:
> >
> > SendKeys "^{Home}"
> >
> > --
> > If this helps, please click the Yes button.
> >
> > Cheers,
> > Shane Devenshire
> >
> >
> > "Mike" wrote:
> >
> > > I have crudely written a macro that takes the cursor to A1 on all worksheets
> > > in a workbook. I'm hoping to add a command that will send the curso to the
> > > upper left of any frozen cells first, then go to A1 (basically I want it to
> > > perform Ctrl-Home first). Does anyone have any advice on this? Below is the
> > > code I am currently using.
> > >
> > > Dim wk As Worksheet
> > > For Each wk In ActiveWorkbook.Worksheets
> > > wk.Activate
> > > Range("A1").Select
> > > Next wk
> > >
> > > Sheets(1).Activate
> > >
> > > Thanks for the help!

Sub ctrlHome()
Cells(Application.ActiveWindow.ScrollRow, _
Application.ActiveWindow.ScrollColumn).Select
End Sub

will take you to the tol-left cell below the frozen pane...
that is what you wanted, right?

Basically Application.ActiveWindow.ScrollRow gives you the rowno and
Application.ActiveWindow.ScrollColumn gives you the columnno of the first
cell...


"Mike" wrote:

> SendKeys isn't working for me.. Sheeloo, can you give me an example of how
> to use those commands in my code?
>
> "Sheeloo" wrote:
>
> > Application.ActiveWindow.ScrollRow
> > and
> > Application.ActiveWindow.ScrollColumn
> > will give you the row and column numbers which you can use in your code...
> >
> >
> >
> > "Shane Devenshire" wrote:
> >
> > > Hi,
> > >
> > > Look at the sendkeys command, here is some code:
> > >
> > > SendKeys "^{Home}"
> > >
> > > --
> > > If this helps, please click the Yes button.
> > >
> > > Cheers,
> > > Shane Devenshire
> > >
> > >
> > > "Mike" wrote:
> > >
> > > > I have crudely written a macro that takes the cursor to A1 on all worksheets
> > > > in a workbook. I'm hoping to add a command that will send the curso to the
> > > > upper left of any frozen cells first, then go to A1 (basically I want it to
> > > > perform Ctrl-Home first). Does anyone have any advice on this? Below is the
> > > > code I am currently using.
> > > >
> > > > Dim wk As Worksheet
> > > > For Each wk In ActiveWorkbook.Worksheets
> > > > wk.Activate
> > > > Range("A1").Select
> > > > Next wk
> > > >
> > > > Sheets(1).Activate
> > > >
> > > > Thanks for the help!

Thanks for the help, but it's still not doing what I want. That does take me
to the top-left cell of the frozen pane, but what I really want is to take
the cursor to the first frozen cell (where it would take you if you pressed
Ctrl+Home).

SendKeys actually does work, but my hotkey is Ctrl-Shift-H, so by holding
shift I highlight everything from the "Home" cell to A1... can I fix this?

"Sheeloo" wrote:

> Sub ctrlHome()
> Cells(Application.ActiveWindow.ScrollRow, _
> Application.ActiveWindow.ScrollColumn).Select
> End Sub
>
> will take you to the tol-left cell below the frozen pane...
> that is what you wanted, right?
>
> Basically Application.ActiveWindow.ScrollRow gives you the rowno and
> Application.ActiveWindow.ScrollColumn gives you the columnno of the first
> cell...
>
>
> "Mike" wrote:
>
> > SendKeys isn't working for me.. Sheeloo, can you give me an example of how
> > to use those commands in my code?
> >
> > "Sheeloo" wrote:
> >
> > > Application.ActiveWindow.ScrollRow
> > > and
> > > Application.ActiveWindow.ScrollColumn
> > > will give you the row and column numbers which you can use in your code...
> > >
> > >
> > >
> > > "Shane Devenshire" wrote:
> > >
> > > > Hi,
> > > >
> > > > Look at the sendkeys command, here is some code:
> > > >
> > > > SendKeys "^{Home}"
> > > >
> > > > --
> > > > If this helps, please click the Yes button.
> > > >
> > > > Cheers,
> > > > Shane Devenshire
> > > >
> > > >
> > > > "Mike" wrote:
> > > >
> > > > > I have crudely written a macro that takes the cursor to A1 on all worksheets
> > > > > in a workbook. I'm hoping to add a command that will send the curso to the
> > > > > upper left of any frozen cells first, then go to A1 (basically I want it to
> > > > > perform Ctrl-Home first). Does anyone have any advice on this? Below is the
> > > > > code I am currently using.
> > > > >
> > > > > Dim wk As Worksheet
> > > > > For Each wk In ActiveWorkbook.Worksheets
> > > > > wk.Activate
> > > > > Range("A1").Select
> > > > > Next wk
> > > > >
> > > > > Sheets(1).Activate
> > > > >
> > > > > Thanks for the help!

I'm confused.

When you manually hit CTRL + Home which cell becomes active?

Should be the top left cell below the "Frozen" area.

i.e. rows 1:5 are frozen.

CTRL + Home will select A6

Rows 1:5 and columns A:C are frozen.

CTRL + Home will select D6

Sheeloo's code does that.

Where do you want to go?


Gord Dibben MS Excel MVP



On Wed, 4 Mar 2009 13:40:01 -0800, Mike <(E-Mail Removed)>
wrote:

>Thanks for the help, but it's still not doing what I want. That does take me
>to the top-left cell of the frozen pane, but what I really want is to take
>the cursor to the first frozen cell (where it would take you if you pressed
>Ctrl+Home).
>
>SendKeys actually does work, but my hotkey is Ctrl-Shift-H, so by holding
>shift I highlight everything from the "Home" cell to A1... can I fix this?
>
>"Sheeloo" wrote:
>
>> Sub ctrlHome()
>> Cells(Application.ActiveWindow.ScrollRow, _
>> Application.ActiveWindow.ScrollColumn).Select
>> End Sub
>>
>> will take you to the tol-left cell below the frozen pane...
>> that is what you wanted, right?
>>
>> Basically Application.ActiveWindow.ScrollRow gives you the rowno and
>> Application.ActiveWindow.ScrollColumn gives you the columnno of the first
>> cell...
>>
>>
>> "Mike" wrote:
>>
>> > SendKeys isn't working for me.. Sheeloo, can you give me an example of how
>> > to use those commands in my code?
>> >
>> > "Sheeloo" wrote:
>> >
>> > > Application.ActiveWindow.ScrollRow
>> > > and
>> > > Application.ActiveWindow.ScrollColumn
>> > > will give you the row and column numbers which you can use in your code...
>> > >
>> > >
>> > >
>> > > "Shane Devenshire" wrote:
>> > >
>> > > > Hi,
>> > > >
>> > > > Look at the sendkeys command, here is some code:
>> > > >
>> > > > SendKeys "^{Home}"
>> > > >
>> > > > --
>> > > > If this helps, please click the Yes button.
>> > > >
>> > > > Cheers,
>> > > > Shane Devenshire
>> > > >
>> > > >
>> > > > "Mike" wrote:
>> > > >
>> > > > > I have crudely written a macro that takes the cursor to A1 on all worksheets
>> > > > > in a workbook. I'm hoping to add a command that will send the curso to the
>> > > > > upper left of any frozen cells first, then go to A1 (basically I want it to
>> > > > > perform Ctrl-Home first). Does anyone have any advice on this? Below is the
>> > > > > code I am currently using.
>> > > > >
>> > > > > Dim wk As Worksheet
>> > > > > For Each wk In ActiveWorkbook.Worksheets
>> > > > > wk.Activate
>> > > > > Range("A1").Select
>> > > > > Next wk
>> > > > >
>> > > > > Sheets(1).Activate
>> > > > >
>> > > > > Thanks for the help!

When I use Sheeloo's code, it takes me to the top left frozen cell *of the
cells I can currently see on my screen*... Not the top left of the frozen
cells as in your example below.

Ideally I would like to put a line in the code below that would take the
cursor (and thereby the view on the screen) to the top left frozen cell, and
then leave the cursor on A1. And repeat for each sheet. The idea is to use
this macro before saving and closing a workbook so that when it is opened
next, the user sees continuous both down and right starting at A1.

Sub Home()

Dim wk As Worksheet
For Each wk In ActiveWorkbook.Worksheets
wk.Activate
Range("A1").Select
Next wk

Sheets(1).Activate

End Sub

Right now, it just goes to A1, but, for example if colums 1:3 are frozen,
the result could be to see colums 1:3 and then 30:50ish if the previous user
was working in the lower range.

Thanks a lot for the help,
Mike

"Gord Dibben" wrote:

> I'm confused.
>
> When you manually hit CTRL + Home which cell becomes active?
>
> Should be the top left cell below the "Frozen" area.
>
> i.e. rows 1:5 are frozen.
>
> CTRL + Home will select A6
>
> Rows 1:5 and columns A:C are frozen.
>
> CTRL + Home will select D6
>
> Sheeloo's code does that.
>
> Where do you want to go?
>
>
> Gord Dibben MS Excel MVP
>
>
>
> On Wed, 4 Mar 2009 13:40:01 -0800, Mike <(E-Mail Removed)>
> wrote:
>
> >Thanks for the help, but it's still not doing what I want. That does take me
> >to the top-left cell of the frozen pane, but what I really want is to take
> >the cursor to the first frozen cell (where it would take you if you pressed
> >Ctrl+Home).
> >
> >SendKeys actually does work, but my hotkey is Ctrl-Shift-H, so by holding
> >shift I highlight everything from the "Home" cell to A1... can I fix this?
> >
> >"Sheeloo" wrote:
> >
> >> Sub ctrlHome()
> >> Cells(Application.ActiveWindow.ScrollRow, _
> >> Application.ActiveWindow.ScrollColumn).Select
> >> End Sub
> >>
> >> will take you to the tol-left cell below the frozen pane...
> >> that is what you wanted, right?
> >>
> >> Basically Application.ActiveWindow.ScrollRow gives you the rowno and
> >> Application.ActiveWindow.ScrollColumn gives you the columnno of the first
> >> cell...
> >>
> >>
> >> "Mike" wrote:
> >>
> >> > SendKeys isn't working for me.. Sheeloo, can you give me an example of how
> >> > to use those commands in my code?
> >> >
> >> > "Sheeloo" wrote:
> >> >
> >> > > Application.ActiveWindow.ScrollRow
> >> > > and
> >> > > Application.ActiveWindow.ScrollColumn
> >> > > will give you the row and column numbers which you can use in your code...
> >> > >
> >> > >
> >> > >
> >> > > "Shane Devenshire" wrote:
> >> > >
> >> > > > Hi,
> >> > > >
> >> > > > Look at the sendkeys command, here is some code:
> >> > > >
> >> > > > SendKeys "^{Home}"
> >> > > >
> >> > > > --
> >> > > > If this helps, please click the Yes button.
> >> > > >
> >> > > > Cheers,
> >> > > > Shane Devenshire
> >> > > >
> >> > > >
> >> > > > "Mike" wrote:
> >> > > >
> >> > > > > I have crudely written a macro that takes the cursor to A1 on all worksheets
> >> > > > > in a workbook. I'm hoping to add a command that will send the curso to the
> >> > > > > upper left of any frozen cells first, then go to A1 (basically I want it to
> >> > > > > perform Ctrl-Home first). Does anyone have any advice on this? Below is the
> >> > > > > code I am currently using.
> >> > > > >
> >> > > > > Dim wk As Worksheet
> >> > > > > For Each wk In ActiveWorkbook.Worksheets
> >> > > > > wk.Activate
> >> > > > > Range("A1").Select
> >> > > > > Next wk
> >> > > > >
> >> > > > > Sheets(1).Activate
> >> > > > >
> >> > > > > Thanks for the help!
>
>

Rows 1:3 are frozen.

User has scrolled down so 1:3 are visible and rows 30:59 are visible

You want the view changed from seeing rows 1:3 and rows 30:59 to continuous
rows 1:32 ?

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


Gord



On Thu, 5 Mar 2009 06:27:01 -0800, Mike <(E-Mail Removed)>
wrote:

>When I use Sheeloo's code, it takes me to the top left frozen cell *of the
>cells I can currently see on my screen*... Not the top left of the frozen
>cells as in your example below.
>
>Ideally I would like to put a line in the code below that would take the
>cursor (and thereby the view on the screen) to the top left frozen cell, and
>then leave the cursor on A1. And repeat for each sheet. The idea is to use
>this macro before saving and closing a workbook so that when it is opened
>next, the user sees continuous both down and right starting at A1.
>
>Sub Home()
>
> Dim wk As Worksheet
> For Each wk In ActiveWorkbook.Worksheets
> wk.Activate
> Range("A1").Select
> Next wk
>
> Sheets(1).Activate
>
>End Sub
>
>Right now, it just goes to A1, but, for example if colums 1:3 are frozen,
>the result could be to see colums 1:3 and then 30:50ish if the previous user
>was working in the lower range.
>
>Thanks a lot for the help,
>Mike
>
>"Gord Dibben" wrote:
>
>> I'm confused.
>>
>> When you manually hit CTRL + Home which cell becomes active?
>>
>> Should be the top left cell below the "Frozen" area.
>>
>> i.e. rows 1:5 are frozen.
>>
>> CTRL + Home will select A6
>>
>> Rows 1:5 and columns A:C are frozen.
>>
>> CTRL + Home will select D6
>>
>> Sheeloo's code does that.
>>
>> Where do you want to go?
>>
>>
>> Gord Dibben MS Excel MVP
>>
>>
>>
>> On Wed, 4 Mar 2009 13:40:01 -0800, Mike <(E-Mail Removed)>
>> wrote:
>>
>> >Thanks for the help, but it's still not doing what I want. That does take me
>> >to the top-left cell of the frozen pane, but what I really want is to take
>> >the cursor to the first frozen cell (where it would take you if you pressed
>> >Ctrl+Home).
>> >
>> >SendKeys actually does work, but my hotkey is Ctrl-Shift-H, so by holding
>> >shift I highlight everything from the "Home" cell to A1... can I fix this?
>> >
>> >"Sheeloo" wrote:
>> >
>> >> Sub ctrlHome()
>> >> Cells(Application.ActiveWindow.ScrollRow, _
>> >> Application.ActiveWindow.ScrollColumn).Select
>> >> End Sub
>> >>
>> >> will take you to the tol-left cell below the frozen pane...
>> >> that is what you wanted, right?
>> >>
>> >> Basically Application.ActiveWindow.ScrollRow gives you the rowno and
>> >> Application.ActiveWindow.ScrollColumn gives you the columnno of the first
>> >> cell...
>> >>
>> >>
>> >> "Mike" wrote:
>> >>
>> >> > SendKeys isn't working for me.. Sheeloo, can you give me an example of how
>> >> > to use those commands in my code?
>> >> >
>> >> > "Sheeloo" wrote:
>> >> >
>> >> > > Application.ActiveWindow.ScrollRow
>> >> > > and
>> >> > > Application.ActiveWindow.ScrollColumn
>> >> > > will give you the row and column numbers which you can use in your code...
>> >> > >
>> >> > >
>> >> > >
>> >> > > "Shane Devenshire" wrote:
>> >> > >
>> >> > > > Hi,
>> >> > > >
>> >> > > > Look at the sendkeys command, here is some code:
>> >> > > >
>> >> > > > SendKeys "^{Home}"
>> >> > > >
>> >> > > > --
>> >> > > > If this helps, please click the Yes button.
>> >> > > >
>> >> > > > Cheers,
>> >> > > > Shane Devenshire
>> >> > > >
>> >> > > >
>> >> > > > "Mike" wrote:
>> >> > > >
>> >> > > > > I have crudely written a macro that takes the cursor to A1 on all worksheets
>> >> > > > > in a workbook. I'm hoping to add a command that will send the curso to the
>> >> > > > > upper left of any frozen cells first, then go to A1 (basically I want it to
>> >> > > > > perform Ctrl-Home first). Does anyone have any advice on this? Below is the
>> >> > > > > code I am currently using.
>> >> > > > >
>> >> > > > > Dim wk As Worksheet
>> >> > > > > For Each wk In ActiveWorkbook.Worksheets
>> >> > > > > wk.Activate
>> >> > > > > Range("A1").Select
>> >> > > > > Next wk
>> >> > > > >
>> >> > > > > Sheets(1).Activate
>> >> > > > >
>> >> > > > > Thanks for the help!
>>
>>

Perfect! Thanks so much--y'all are always such a huge help.

"Gord Dibben" wrote:

> Rows 1:3 are frozen.
>
> User has scrolled down so 1:3 are visible and rows 30:59 are visible
>
> You want the view changed from seeing rows 1:3 and rows 30:59 to continuous
> rows 1:32 ?
>
> Application.Goto Range("A1"), Scroll:=True
>
>
> Gord
>
>
>
> On Thu, 5 Mar 2009 06:27:01 -0800, Mike <(E-Mail Removed)>
> wrote:
>
> >When I use Sheeloo's code, it takes me to the top left frozen cell *of the
> >cells I can currently see on my screen*... Not the top left of the frozen
> >cells as in your example below.
> >
> >Ideally I would like to put a line in the code below that would take the
> >cursor (and thereby the view on the screen) to the top left frozen cell, and
> >then leave the cursor on A1. And repeat for each sheet. The idea is to use
> >this macro before saving and closing a workbook so that when it is opened
> >next, the user sees continuous both down and right starting at A1.
> >
> >Sub Home()
> >
> > Dim wk As Worksheet
> > For Each wk In ActiveWorkbook.Worksheets
> > wk.Activate
> > Range("A1").Select
> > Next wk
> >
> > Sheets(1).Activate
> >
> >End Sub
> >
> >Right now, it just goes to A1, but, for example if colums 1:3 are frozen,
> >the result could be to see colums 1:3 and then 30:50ish if the previous user
> >was working in the lower range.
> >
> >Thanks a lot for the help,
> >Mike
> >
> >"Gord Dibben" wrote:
> >
> >> I'm confused.
> >>
> >> When you manually hit CTRL + Home which cell becomes active?
> >>
> >> Should be the top left cell below the "Frozen" area.
> >>
> >> i.e. rows 1:5 are frozen.
> >>
> >> CTRL + Home will select A6
> >>
> >> Rows 1:5 and columns A:C are frozen.
> >>
> >> CTRL + Home will select D6
> >>
> >> Sheeloo's code does that.
> >>
> >> Where do you want to go?
> >>
> >>
> >> Gord Dibben MS Excel MVP
> >>
> >>
> >>
> >> On Wed, 4 Mar 2009 13:40:01 -0800, Mike <(E-Mail Removed)>
> >> wrote:
> >>
> >> >Thanks for the help, but it's still not doing what I want. That does take me
> >> >to the top-left cell of the frozen pane, but what I really want is to take
> >> >the cursor to the first frozen cell (where it would take you if you pressed
> >> >Ctrl+Home).
> >> >
> >> >SendKeys actually does work, but my hotkey is Ctrl-Shift-H, so by holding
> >> >shift I highlight everything from the "Home" cell to A1... can I fix this?
> >> >
> >> >"Sheeloo" wrote:
> >> >
> >> >> Sub ctrlHome()
> >> >> Cells(Application.ActiveWindow.ScrollRow, _
> >> >> Application.ActiveWindow.ScrollColumn).Select
> >> >> End Sub
> >> >>
> >> >> will take you to the tol-left cell below the frozen pane...
> >> >> that is what you wanted, right?
> >> >>
> >> >> Basically Application.ActiveWindow.ScrollRow gives you the rowno and
> >> >> Application.ActiveWindow.ScrollColumn gives you the columnno of the first
> >> >> cell...
> >> >>
> >> >>
> >> >> "Mike" wrote:
> >> >>
> >> >> > SendKeys isn't working for me.. Sheeloo, can you give me an example of how
> >> >> > to use those commands in my code?
> >> >> >
> >> >> > "Sheeloo" wrote:
> >> >> >
> >> >> > > Application.ActiveWindow.ScrollRow
> >> >> > > and
> >> >> > > Application.ActiveWindow.ScrollColumn
> >> >> > > will give you the row and column numbers which you can use in your code...
> >> >> > >
> >> >> > >
> >> >> > >
> >> >> > > "Shane Devenshire" wrote:
> >> >> > >
> >> >> > > > Hi,
> >> >> > > >
> >> >> > > > Look at the sendkeys command, here is some code:
> >> >> > > >
> >> >> > > > SendKeys "^{Home}"
> >> >> > > >
> >> >> > > > --
> >> >> > > > If this helps, please click the Yes button.
> >> >> > > >
> >> >> > > > Cheers,
> >> >> > > > Shane Devenshire
> >> >> > > >
> >> >> > > >
> >> >> > > > "Mike" wrote:
> >> >> > > >
> >> >> > > > > I have crudely written a macro that takes the cursor to A1 on all worksheets
> >> >> > > > > in a workbook. I'm hoping to add a command that will send the curso to the
> >> >> > > > > upper left of any frozen cells first, then go to A1 (basically I want it to
> >> >> > > > > perform Ctrl-Home first). Does anyone have any advice on this? Below is the
> >> >> > > > > code I am currently using.
> >> >> > > > >
> >> >> > > > > Dim wk As Worksheet
> >> >> > > > > For Each wk In ActiveWorkbook.Worksheets
> >> >> > > > > wk.Activate
> >> >> > > > > Range("A1").Select
> >> >> > > > > Next wk
> >> >> > > > >
> >> >> > > > > Sheets(1).Activate
> >> >> > > > >
> >> >> > > > > Thanks for the help!
> >>
> >>
>
>