This is the first technical post on this blog, which I will provide a quick solution (Excel VBA macro) to synchronize the position of selected cells (not the value) in several worksheets. I will also provide an introduction to a case where I ended up using this macro. Perhaps the way I used can give you some new ideas.

If you are only interested in the Excel macro, skip the initial part and go to section Excel Macro.

Background

Many years ago, while working on-site to support a customer from my previous employer, I decided to develop a small program that allows the customer to generate an Excel file with the current “visual map” of the pick channels in their automated warehouse. The initial objective was to provide a way to visualize and quickly identify how different items (products) categories are spread across workstations due to system performance reasons. A second objective was to identify how items with a high pick rate are located on each workstation, as they could be far from the middle, causing pick performace issues. This information was not available in WMS (Warehouse Management System) because it was not intially specified. With the customer able to quickly view potential problems, they could take the appropriate action.

I won’t dive deep into the details about the warehouse, explaining how everything works, as this would be extremely long. However, I will provide enough information for you to understand the Excel file (report) I created and why I needed to use the macro to synchronize cells between the worksheets.

Warehouse

The warehouse has 4180 pick channels (or supply locations) in an area called Pick By Light. Each channels can have an item assigned to it.

I suggest you take a look at the quick video below for a quick overview of this area.

Each item has many different attributes. Some of them are:

• ID
• Size
• Weight
• Category
• Security

In addition to the attributes, there is other important information about them. For example:

• AWI (Average Weekly Issues)
• Stock on hand
• Last pick date

There are not a number of issues that would require the inventory manager to take action to avoid potential issues. Some are:

• High AWI items should be close to the center of the workstation, preventing the picker from constantly walking long distances
• Heavy items should not be located in high pick channel due to health and safety reasons
• Specific category items must be close to or on the same workstation and not scattered
• Security items must not be located in the Pick By Light area

People who are not familiar the distribution and organization of products in a warehouse can assume that, after solving the above problems, the problem will no longer occur in future. However, this is not right. Below are just a few reasons that explain the need for constant checking:

• AWI changes from time to time due to promotions, season change, etc.
• The WMS can automatically assign new items automatically to “wrong” channels, as there may not be an “ideal” channel available. New items appear in the marked, or even a change in the package may result in an item being considered as new
• Human error. It’s not always simple to manage more than 10,000 different small items in a large warehouse

Excel Template

With the brief introduction, I will briefly explain the Excel file in which I needed to use the macro.

This is how the template looks like:

It took me a few hours to prepare the template. I tried to create the template with a clean appearance and based on the physical layout, so that people could easily understand.

Due to the different attributes and relevant information, I simply duplicated the same layout accross multiple worksheets.

The following screenshot animation shows the template filled with data extracted from the database. There is a also a lot of detailed information that can be found as a note in each cell related to the information shown.

Reason for a Macro

The reason for the Excel macro is simple. When a problem is found on a specific worksheet, you select the cell and switch to the Item ID worksheet to find the respective ID or to the Location worksheet to find the exact naming of the location. This way, the Inventory Manager can take the appropriate action on the WMS. Without the macro, we would need to copy the cell address from the Name Box, change the worksheet and paste the content at the Name Box. This is a simple copy and paste and would be acceptable if you would only check once here and there, but it is quite annoying when you have to do it frequently in multiple worksheets.

In the simple example below, the idea is to identify items with the security attribute, which were incorrectly allocated in the Pick By Light area. The steps are:

1. Zoom out (or just scroll) to see and identify if a problem can be found due to color coding
2. Zoom in and select the cell
3. Change to the Item ID worksheet, copy the ID After that, through the WMS, the inventory manager would move the item to an appropriate area.

Excel Macro

The feature of synchronizing a selected cell (or even a range of cells) is not avaialble in Excel.

If you use multisheet workbooks, you probably know that Excel can’t synchronize the sheets in a workbook. In other words, there is no automatic way to force all sheets to have the same selected range and upper-left cell. The VBA macro that follows uses the active worksheet as a base and then performs the following on all other worksheets in the workbook

Source: Book Excel 2013 Power Programming with VBA by John Walkenbach

This book and also the current version (Excel 2019 Power Programming with VBA) provide a VBA macro to do this synchronization. However, it is necessary to run the macro manually. So there is a small improvement that can be made.

If you need information on how to enable the Developer tab and how to create and use a macro, I suggest checking out the Create Macro page at Excel Easy.

The original VBA macro from the book:

Sub SynchSheets()
    ' Duplicates the active sheet's active cell and upper-left cell 
    ' accross all worksheets

    If TypeName(ActiveSheet) <> "Worksheet" Then Exit Sub

    Dim UserSheet As Worksheet, sht As Worksheet
    Dim TopRow As Long, LeftCol As Long
    Dim SerSel As String

    Application.ScreenUpdating = False

    ' Remember the current sheet
    Set UserSheet = ActiveSheet

    ' Store info from the current active sheet
    
    TopRow = ActiveWindow.ScrollRow
    LeftCol = ActiveWindow.ScrollColumn
    UserSel = ActiveWindow.RangeSelection.Address
    
    ' Loop through the worksheets
    For Each sht In ActiveWorkbook.Worksheets
        If sht.Visible Then 'skip hidden sheets
            sht.Activate
            Range(UserSel).Select
            ActiveWindow.ScrollRow = TopRow
            ActiveWindow.ScrollColumn = LeftCol
        End If
    Next sht

    UserSheet.Activate
    Application.ScreenUpdating = True
End Sub

If you open the Visual Basic Editor for a spreadsheet and paste this code into ThisWorkbook, nothing will happen when you change a cell and switch to a different worksheets, until you run the macro manually.

My first attempt was to call this SynchSheets procedure using the Workbook_SheetSelectionChange event:

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
  SynchSheets
End Sub

But as the Excel file contains 16 worksheets with lots of data, even on a fast computer, Excel was freezing for a short period of time after changing the selected cell.

The reason for the “freeze” is that, when looping through the other worksheets and changing the selected cell, the same Workbook_SheetSelectionChange event is triggered executing the SynchSheets again (and again…), but this is not the desired behaviour, as it is not required to run more than once. To avoid this, I had to set the Application.EnableEvents property to False and, in the end, back to True.

Below the changed procedure SynchSheets highlighting the lines added and the event Workbook_SheetSelectionChange:

Sub SynchSheets()
    ' Duplicates the active sheet's active cell and upper-left cell 
    ' accross all worksheets

    If TypeName(ActiveSheet) <> "Worksheet" Then Exit Sub

    Dim UserSheet As Worksheet, sht As Worksheet
    Dim TopRow As Long, LeftCol As Long
    Dim SerSel As String

    Application.EnableEvents = False
    Application.ScreenUpdating = False

    ' Remember the current sheet
    Set UserSheet = ActiveSheet

    ' Store info from the current active sheet
    
    TopRow = ActiveWindow.ScrollRow
    LeftCol = ActiveWindow.ScrollColumn
    UserSel = ActiveWindow.RangeSelection.Address
    
    ' Loop through the worksheets
    For Each sht In ActiveWorkbook.Worksheets
        If sht.Visible Then 'skip hidden sheets
            sht.Activate
            Range(UserSel).Select
            ActiveWindow.ScrollRow = TopRow
            ActiveWindow.ScrollColumn = LeftCol
        End If
    Next sht

    UserSheet.Activate
    Application.ScreenUpdating = True

    Application.EnableEvents = True
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
  SynchSheets
End Sub

This solution works well, but I ended up changing a bit and using another event called Workbook_SheetDeactivate. In my opinion, this is a better way, because it does not change the cells on all worksheet all the time, but only when the worksheet is changed. The disadvantage of using this last method, is that we see the selected cell “jumping” from the one cell to another after a new worksheet is selected (once only after a cell change). There are some comments in the code below and it should be easy for anyone to follow.

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
    
    If TypeName(Sh) <> "Worksheet" Then Exit Sub
    
    Dim NewWorkSheet As Worksheet, sht As Worksheet
    Dim TopRow As Long, LeftCol As Integer
    Dim UserSel As String
            
    Application.EnableEvents = False
    Application.ScreenUpdating = False

'   Remember the activated worksheet
    Set NewWorkSheet = ActiveSheet

'   Activate the previous Worksheet to retrieve the cell
    Sh.Activate
    
'   Store info from the active sheet
    TopRow = ActiveWindow.ScrollRow
    LeftCol = ActiveWindow.ScrollColumn
    UserSel = ActiveWindow.RangeSelection.Address
    
'   Loop through the worksheets
    For Each sht In ActiveWorkbook.Worksheets
        If sht.Visible Then 'skip hidden sheets
            sht.Activate
            Range(UserSel).Select
            ActiveWindow.ScrollRow = TopRow
            ActiveWindow.ScrollColumn = LeftCol
        End If
    Next sht
    
'   Restore the stored worksheet
    NewWorkSheet.Activate
    Application.ScreenUpdating = True
    Application.EnableEvents = True
End Sub

I hope you can find a use for the macro. Feel free to contact me if you have any questions or have improvementes you would like to share.

Note: The warehouse mentioned above was build in 2007. The most recent warehouses are much more advanced, they are fully automated (Automated Case Picking)

Disclaimer: The data that appears in the screenshots has been manipulated for presentation purposes and confidentiality reasons. The other details about the customer and warehouse can be found, for example, in the available videos that were previously authorized to the public.