Hi all,
I would like to adjust the zoom level of certain sheets in my workbook based on different selection.
The first one is the current code that I am using and it's working:
Sub AdjustZoom()
'/===========================================================================================================================
'/ Adjust zoom level to optimal
'/===========================================================================================================================
Dim wsExec, wsUB, wsTL, wsAPS As Worksheet '/WS in this WB
Dim rngSelection1, rngSelection2, rngSelection3, rngSelection4 As range '/Range in this WB
'/ ===================================================
Set wsExec = Sheets("Executive Summary") '/ "Executive Summary" sheet
Set wsUB = Sheets("Executive Breakdown") '/ "Executive Breakdown" sheet
Set wsTL = Sheets("Team Leader") '/ "Team Leader" sheet
Set wsAPS = Sheets("APS") '/ "APS" sheet
Set rngSelection1 = Sheets("Executive Summary").range("F11:X11") '/ Range of selection to be zoomed for "Executive Summary" sheet
Set rngSelection2 = Sheets("Executive Breakdown").range("L11:AA11") '/ Range of selection to be zoomed for "Executive Breakdown" sheet
Set rngSelection3 = Sheets("Team Leader").range("G12:AA12") '/ Range of selection to be zoomed for "Team Leader" sheet
Set rngSelection4 = Sheets("APS").range("I11:Y11") '/ Range of selection to be zoomed for "APS" sheet
'/ ===================================================
'/ Automatically maximizing the WB
Application.WindowState = xlMaximized
'/ ===================================================
'/ Adjusts zoom level for "Executive Summary" sheet
wsExec.Activate
rngSelection1.Select
ActiveWindow.Zoom = True
'/ ===================================================
'/ Adjusts zoom level for "Executive Breakdown" sheet
wsUB.Activate
rngSelection2.Select
ActiveWindow.Zoom = True
'/ ===================================================
'/ Adjusts zoom level for "Team Leader" sheet
wsTL.Activate
rngSelection3.Select
ActiveWindow.Zoom = True
'/ ===================================================
'/ Adjusts zoom level for "APS" sheet
wsAPS.Activate
rngSelection4.Select
ActiveWindow.Zoom = True
End Sub
However, I would like to compile the code and comes out with this:
Sub Zoom()
Dim wsExec, wsUB, wsTL, wsAPS As Worksheet '/WS in this WB
Dim rngSelection1, rngSelection2, rngSelection3, rngSelection4 As range '/Range in this WB
Dim ws As Worksheet
Dim rng As range
Set wsExec = Sheets("Executive Summary") '/ "Executive Summary" sheet
Set wsUB = Sheets("Executive Breakdown") '/ "Executive Breakdown" sheet
Set wsTL = Sheets("Team Leader") '/ "Team Leader" sheet
Set wsAPS = Sheets("APS") '/ "APS" sheet
Set rngSelection1 = Sheets("Executive Summary").range("F11:X11") '/ Range of selection to be zoomed for "Executive Summary" sheet
Set rngSelection2 = Sheets("Executive Breakdown").range("L11:AA11") '/ Range of selection to be zoomed for "Executive Breakdown" sheet
Set rngSelection3 = Sheets("Team Leader").range("G12:AA12") '/ Range of selection to be zoomed for "Team Leader" sheet
Set rngSelection4 = Sheets("APS").range("I11:Y11") '/ Range of selection to be zoomed for "APS" sheet
'/ ===================================================
'/ Automatically maximizing the WB
Application.WindowState = xlMaximized
For Each ws In ThisWorkbook.Worksheets
Select Case ws.Name
Case "Executive Summary": rng = rngSelection1
Case "Executive Breakdown": rng = rngSelection2
Case "Team Leader": rng = rngSelection3
Case "APS": rng = rngSelection4
Case Else
End Select
With ws
.Activate
.rng.Select
ActiveWindow.Zoom = True
End With
Next ws
End Sub
Unfortunately, it gives complie error here
Can anyone please help me to rectify the code?
Or show me what've I done wrong?
Thank you!
Bookmarks