MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Align all worksheets to same cell & window position

Posted by Jim on February 15, 2002 12:46 PM

I got some VBA code from one of the great Excel sites a while back that I can't find now. It caused every worksheet in the workbook to be positioned the same in the window and have the same active cell, i.e. launch it while active cell is FF5000 and every sheet would align with FF5000 in top left of window position. Would truly appreciate if anyone could point me to it again.

Posted by Escalus on February 15, 2002 4:10 PM

Try this .....

Sub Scroll_To_ActiveCell()
Dim ws As Worksheet, rng As String
rng = ActiveCell.Address(False, False)
Application.ScreenUpdating = False
For Each ws In ActiveWorkbook.Worksheets
With ActiveWindow
.ScrollRow = Range(rng).Row
.ScrollColumn = Range(rng).Column
End With
End Sub

Posted by Jim on February 18, 2002 6:37 AM

Not quite

This gets partway there. It positions all worksheets the same in the window but doesn't make the same cell the active cell in each. Ideally, it would also return to the sheet that was selected as the active sheet upon completion.