+ Reply to Thread
Results 1 to 3 of 3

Auto adjust sheets to specific range selection

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    11-21-2013
    Location
    KL
    MS-Off Ver
    Excel 2010
    Posts
    168

    Auto adjust sheets to specific range selection

    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
     .rng.Select
    Can anyone please help me to rectify the code?
    Or show me what've I done wrong?

    Thank you!

  2. #2
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    work: 2016 on Win10 (notebook), private: 2019 on Win10 (desktop), 2019 on Win11 (notebook)
    Posts
    8,198

    Re: Auto adjust sheets to specific range selection

    Hi, hcyeap,

    you would need to use
    Set rng = rngSelection1
    Maybe use this approach:
    Sub AdjustZoom2()
    '/===========================================================================================================================
    '/  Adjust zoom level to optimal
    '/===========================================================================================================================
        Dim varSheets As Variant
        Dim varRanges As Variant
        Dim lngArray As Long
        
        varSheets = Array("Executive Summary", "Executive Breakdown", "Team Leader", "APS")
        varRanges = Array("F11:X11", "L11:AA11", "G12:AA12", "I11:Y11")
        Application.WindowState = xlMaximized
        
        For lngArray = LBound(varSheets) To UBound(varSheets)
          Sheets(varSheets(lngArray)).Activate
          Range(varRanges(lngArray)).Select
          ActiveWindow.Zoom = True
        Next lngArray
    End Sub
    Ciao,
    Holger
    Last edited by HaHoBe; 03-04-2014 at 02:33 AM. Reason: corrected the first code
    Use Code-Tags for showing your code: [code] Your Code here [/code]
    Please mark your question Solved if there has been offered a solution that works fine for you

  3. #3
    Forum Contributor
    Join Date
    11-21-2013
    Location
    KL
    MS-Off Ver
    Excel 2010
    Posts
    168

    Re: Auto adjust sheets to specific range selection

    Hi Holger,

    Your code working flawlessly!
    Thank you very much!

    Regards.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Macro to Auto-adjust print area in a multiple sheets (excel 2010)
    By ALNER in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-31-2014, 10:11 AM
  2. [SOLVED] Auto populate adjacent cells based on specific dropdown selection
    By ahtigers10 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 01-02-2014, 08:11 PM
  3. How do you adjust the auto filter range?
    By ulidech in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-20-2013, 01:34 PM
  4. [SOLVED] Auto Adjust Stacked Column Need to Adjust Legend
    By tbren in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 03-29-2013, 01:56 PM
  5. Range variable doen't adjust to moving cells between sheets
    By gvz2009 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 01-03-2009, 11:45 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1