Reset Scroll bar to A1 across multiple sheets

Grimm127

Board Regular
Joined
Aug 17, 2011
Messages
118
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
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,993
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.

VBA Code:
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

Board Regular
Joined
Aug 17, 2011
Messages
118
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.

VBA Code:
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

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,993
What Freeze Panes? What do you mean "resettig freeze panes"

VBA Code:
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
 
Solution

Watch MrExcel Video

Forum statistics

Threads
1,130,309
Messages
5,641,447
Members
417,209
Latest member
Agbarker

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top