Reset Scroll bar to A1 across multiple sheets

[Grimm127]

Hi All you Guru's

Need a little help with something. I am trying to reset the scroll bar back to A! across a multiple sheets. Currently I have this code. I been trying to add the the scroll reset using the ws.scrollrow=1 or ActiveSheet.UsedRange or application.goto Reference:=Range("a1"), scroll:=True but none of these three worked. Also I am not sure where to place them either. Can you help me? Thanks!


Sub Hide_Columns_Via_CoverPage()

' Hides Columns that are not within the Period

Application.ScreenUpdating = True

Dim ws As Variant, Xrow As Long, LCol As Long

For Each ws In Worksheets(Array("Income Statement", "Balance Sheet", "Cash Flow Statement"))

Xrow = 5
LCol = ws.Cells(5, Columns.Count).End(xlToLeft).Column

With ws
For x = 1 To LCol
If ws.Cells(Xrow, x) <> "X" Then
ws.Columns(x).Hidden = True
Else
ws.Columns(x).Hidden = False
End If
Next x
End With

 

[mikerickson]

I don't see anything in that code that moves the scroll location. But this worked for me. I was suprised that there was no flicker even without changing Application.ScreenUpdating. If you get flicker you could use that to calm it.

Dim currentSheet as Worksheet

Set currentSheet = ActiveSheet

For Each ws In Worksheets(Array("Income Statement", "Balance Sheet", "Cash Flow Statement"))
    Application.Goto ws.Range("A1")
next ws

currentSheet.Activate

 

[Grimm127]

I don't see anything in that code that moves the scroll location. But this worked for me. I was suprised that there was no flicker even without changing Application.ScreenUpdating. If you get flicker you could use that to calm it.

Dim currentSheet as Worksheet

Set currentSheet = ActiveSheet

For Each ws In Worksheets(Array("Income Statement", "Balance Sheet", "Cash Flow Statement"))
    Application.Goto ws.Range("A1")
next ws

currentSheet.Activate

It sort of works. The problem lies that the freeze panes are not resetting so it may go to A1 but it doesn't reset the scroll. so the sheet is way to the right and a few rows below instead of scrolling back to its inital postion.

 

[mikerickson]

What Freeze Panes? What do you mean "resettig freeze panes"

For Each ws In Worksheets(Array("Income Statement", "Balance Sheet", "Cash Flow Statement"))
    Application.Goto ws.Range("A1")
    With ActiveWindow
        For Each onePane in .Panes
            onePane.ScrollRow = 1
            onePane.ScrollColumn = 1
        Next onePane
    End With
next ws