Probably an easy VBA question for someone who knows what they're doing

[pam53154]

I am trying to automate doing the following to each tab in a workbook with a lot of tabs.

Set the zoom to 100%
Unfilter data
Expand all row and column groupings
Control + home (so that when the next user opens the spreadsheet the view is of the top left of the spreadsheet)

So far I have a macro which flips to each tab and does all of this. But, it is a pain to update this each time a tab is added or deleted. I suspect there is an easy way to loop through each tab without naming each tab but I don't know enough about VBA to do it myself.

Thanks!

 

[andrewkard]

Hello, try:

Sub test()
    Dim i%
    For i = 1 To Sheets.Count
        Worksheets(i).Activate
        On Error Resume Next:Selection.AutoFilter
        Cells.Select
        Selection.EntireRow.Hidden = False
        Selection.EntireColumn.Hidden = False
        ActiveWindow.Zoom = 100
        Cells(1,1).select
    Next i
End Sub

 

[Andrew Poulsom]

You can loop through your worksheets like this:

Dim ws As Worksheet
For Each ws in ActiveWorkbook.Worksheets
'    Code to format ws
Next ws

Inside the loop you can use ws in place of a specific worksheet reference, eg Worksheets("Sheet1").

 

[pam53154]

Many thanks! This is much better than I had.

Instead of the top left always being cell A1, could it be where the panes are frozen? For example, if I run the macro while the cursor is in a random cell, say Z20, and the panes are frozen at G3, going to cell A1 will leave most of the columns between G and Z unviewable. Any thoughts? Control + home is the key stroke that would get you to where the freeze panes is, but I don't know how to put that in code.

Thanks!!

 

[pam53154]

I adapted this from information I found on internet searches which will go to the home cell if the panes are frozen, otherwise to cell A1. Thanks again for your help!

Dim lngRowNumber As Long, _
lngColNumber As Long
Dim strColLetter As String

For Each ws In ThisWorkbook.Worksheets
ws.Activate

With ActiveWindow
If .SplitRow = 0 And .SplitColumn = 0 Then
Range("A1").Select
Else
lngRowNumber = .SplitRow + 1
lngColNumber = .SplitColumn + 1
strColLetter = Chr(lngColNumber + 64)
Range(strColLetter & lngRowNumber).Select
End If
End With
Next