AIM: Synchronize the scrollbars, zoom level and range selection for all worksheets in the workbook.

NOTE: If you get stuck check the sample code in the downloadable Project workbook.

---

Why create this Macro?

1. The need: Let's say you have a workbook with 10 worksheets in it, and after working on this for a couple of hours you find that every worksheet has a different cell selection, different zoom level and different scroll position. You want to synchronize all worksheets to same view as the Activeworkbook (for instance it’s set to cell B2, with zoom level 100% and with cell A1 in the top left corner) so that it’s ready to share with your manager.
2. Without the Macro: In Excel you need to do this one worksheet at a time, by scrolling the windows to the same positions, selecting the zoom level and selecting the same cell (B2).
3. So what?: Doing this in Excel without the Macro can be a real pain if you have lots of worksheets with different views – even if you’re a PRO with keyboard shortcuts. Why isn’t there a button in Excel to do this?
4. The solution: Write a VBA macro that synchronizes ALL worksheets to the active worksheet with one click, getting you the same result INSTANTLY. You can even save this macro in your Personal Macro Workbook and assign it to your Quick Access Toolbar for easy access in any workbook.
   
   

---

Set up workbook, create procedure and declare variables

1. Open a new Excel workbook and access the VBA Editor
   
   
2. Create a new code module called Module1
   
   
3. Create a new procedure called Sub Syncrhonize_Worksheets_to_ActiveSheet_View()
   
   
4. Declare new variables inside the procedure (1) Dim “Wks_Selected” as Worksheet, (2) Dim “Wks” as Worksheet, (3) Dim “View_Row” as Long, (4) Dim “View_Col” as Long, (5) Dim “View_Zoom” as Variant, (6) Dim “View_Selected” as String
   
   

Extract view information from Active Sheet

1. Set the Wks_Selected to the ActiveSheet
   
   
2. Extract the view information from the ActiveSheet and place into each of the relevant variables: View_Row, View_Col, View_Zoom and View_Selected
   
   
3. To get the row: ActiveWindow.ScrollRow
   
   
4. To get the col: ActiveWindow.ScrollRow
   
   
5. To get the zoom: ActiveWindow.Zoom
   
   
6. To get the range selected: ActiveWindow.RangeSelection.Address
   
   

Loop through each worksheet and apply view information

1. Write a For Each Loop (see module 5 for refresher) that loops through each worksheet
   
   
2. With each worksheet in turn, check if the worksheet is visible using an IF… THEN… statement
   
   
3. IF the worksheet is visible THEN … synchronize the view with the stored view information (remember: close the IF THEN statement with End If)
   
   
4. Close the For Each Loop (remember: use the keywork Next)
   
   
5. Finally, restore the original view by activating Wks_Selected (which stored the original ActiveSheet)
   
   

Save your VBA code and Test it

1. Save your workbook with a suitable name (e.g. M6 Project 4)
   
   
2. Compare your VBA code with the sample code in the downloadable Module 6 workbook
   
   
3. Create a new workbook with 5 worksheets
   
   
4. Change the scroll setting, zoom level and range selection on each worksheet
   
   
5. Run the Sub and check that it syncs all visible worksheets to the active worksheet view settings

---

Important reminder about how to learn faster

In Module 1 we learned that everyone needs rest / exercise to learn better.

So take breaks when you can.

And you WILL learn faster!

Also find ways to practice your new skills…

Either in your day to day work…

Or by teaching someone else.

Sometimes the BEST way to reinforce your learning is to TEACH others!

---

Write your comments and feedback below

• If you have questions or comments about this lesson, write it in the section below.
• Alternatively you can email the instructor Victor at [email protected]