Setting all Excel sheets at a defined zoom level

Question

I have more than twenty sheets in an Excel workbook (file). Is there some code snippet or a command I could apply/use so that all sheets could be reset to let's say 85% zoom level?

score 65 · Accepted Answer · edited Dec 24 '19 at 12:34

65

Sub SetZoom()
    Dim ws As Worksheet

    For Each ws In Worksheets
        ws.Select
        ActiveWindow.Zoom = 85 ' change as per your requirements
    Next ws
End Sub

BTW, if you simply select all worksheets in your workbook using the tabs you can then set the zoom to 85% and it will apply to all worksheets

edited Dec 24 '19 at 12:34

ProfK

49,207
121
399
775

answered May 10 '11 at 17:11

Alex P

12,249
5
51
70

@MoritzSchmitzv.Hülst Use VBA `=P` – ADTC Nov 13 '15 at 16:20
While this solution still requires you to use `.Select`, you can update the zoom for all sheets at once without looping: https://stackoverflow.com/a/39715890/2727437 – Marcucciboy2 Sep 04 '18 at 15:42

Felipe Ribeiro · Answer 2 · 2019-10-31T19:50:09.207

1

Sub SetZoom()

Dim ws As Worksheet
Application.ScreenUpdating = False    'Optional
For Each ws In ActiveWorkbook.Worksheets
    ws.Activate
    ActiveWindow.Zoom = 85
Next
Application.ScreenUpdating = True

End Sub

This code is similar from the above, but it is not necessary to select all worksheets in your workbook before running the macro. Instead of using ws.Select and Next ws that not work correctly unless you select the worksheets, change to ws.Activate and Next to set the zoom for all the sheets. As optional, the ScreenUpdating can be disabled for a workbook with a lot of sheets.

edited Oct 31 '19 at 19:50

answered Oct 31 '19 at 17:38

Felipe Ribeiro

41
6

While this code may help with the question, it is better to include some context, explaining how it works and when to use it. Code-only answers tend to be less useful in the long run. See [How do I write a good answer?](https://stackoverflow.com/help/how-to-answer) for some more info. – Klaus Gütter Oct 31 '19 at 18:32
Thanks for the feedback Klaus! – Felipe Ribeiro Oct 31 '19 at 19:51

score 0 · Answer 3 · edited Apr 14 '20 at 16:07

0

Option Explicit

Sub FixSheets()
    Dim ws As Worksheet
    For Each ws In Worksheets
        ws.Activate
        ws.UsedRange.Select
        ActiveWindow.Zoom = True 'Zoom sur la sélection
        ActiveCell.Select
    Next ws
End Sub

edited Apr 14 '20 at 16:07

Paul Roub

36,322
27
84
93

answered Apr 14 '20 at 15:51

Ardus

11
2

Setting all Excel sheets at a defined zoom level

3 Answers3

Linked