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

pam53154

Board Regular
Joined
Mar 3, 2009
Messages
107
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!
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
Hello, try:
Code:
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
 
Last edited:
Upvote 0
You can loop through your worksheets like this:

Code:
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").
 
Last edited:
Upvote 0
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!!
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,211,788
Messages
6,103,975
Members
447,888
Latest member
eaydogan

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
Back
Top