Share

How to synchronize data between two sheets or ranges in Excel

In this article, we will have a look at how to synchronize the data between two cells / ranges / columns or rows using VBA. Of course, you can use cell reference to do the same, but in cases where you need to use VBA, you can refer to the following examples.

In each of the below examples, we will use the “Worksheet_Change” subroutine. This subroutine is fired whenever there is a change in the values within a worksheet. The parameter to this subroutine is Target (of type Range) which represents the range in which the change was made. The code needs to be written in the code module of the sheet from which the changes need to be synchronized.

Example 1: Synchronize data between two cells

Let’s say, you have two dashboards wherein if you enter the date in the first dashboard, the date should automatically be reflected in the second dashboard. In our example below, we will sync cell A1 in Sheet1 automatically to the same cell A1 in Sheet2. As mentioned above, please note that the code should be written in the code module of Sheet1. Here is the code.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim sourceSheet As Worksheet, targetSheet As Worksheet
    Dim syncCell As String

    'Set the source and target sheets here
    Set sourceSheet = Sheet1
    Set targetSheet = Sheet2

    'This will be the cell that needs to be synced
    syncCell = "A1"
    
    'Check whether the cell that was changed in Sheet1 is A1
    If Target.Address = Range(syncCell).Address Then
        'If so, copy the value to Sheet2, cell A1
        targetSheet.Range(Target.Address) = sourceSheet.Range(Target.Address)
    End If

End Sub

We use Target.Address to find the cell in which the change was made. It will return the address in the format “$A$1”.

So, whenever you make changes in Sheet1 cell A1, the value will be automatically copied to the corresponding cell in Sheet2. If you clear the cell, then the cell in Sheet2 will also be cleared. Note that, only the values will be copied, not the formula or the formatting. If you need to copy the formatting use:

    If Target.Address = Range(syncCell).Address Then
        sourceSheet.Range(Target.Address).Copy
        targetSheet.Range(Target.Address).PasteSpecial xlPasteAll
    End If

 

And on the same lines, for copying formulas use:

    If Target.Address = Range(syncCell).Address Then
        sourceSheet.Range(Target.Address).Copy
        targetSheet.Range(Target.Address).PasteSpecial xlPasteFormulasAndNumberFormats
    End If

In this case, if you enter a value, the value will be synced and if you enter a formula, the formula will be synced

Example 2: Synchronize data between two rows

Now, let us see how to sync an entire row between two sheets. For this example, we will sync row 1 in Sheet1 to corresponding row in Sheet2

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim sourceSheet As Worksheet, targetSheet As Worksheet
    Dim syncRow As Long

    'Set the source and target sheets here
    Set sourceSheet = Sheet1
    Set targetSheet = Sheet2

    'This will be the row that needs to be synced
    syncRow = 1
    
    'Check whether the cell that was changed in Sheet1 lies in Row 1
    If Target.Row = syncRow Then
        'If so, get the address of the cell that changed
        'And make the same change in corresponding cell in Sheet2
        targetSheet.Range(Target.Address) = sourceSheet.Range(Target.Address)
    End If

End Sub

 

Here we use the Target.Row property to get the row number of the cell that changed. So, if you make change any cell in row 1 the value will be copied over to the same cell in row 2

Example 3: Synchronize data between two columns

Syncing two columns is very similar to the syncing of two rows. Here is the code to sync column 1:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim sourceSheet As Worksheet, targetSheet As Worksheet
    Dim syncCol As Long

    'Set the source and target sheets here
    Set sourceSheet = Sheet1
    Set targetSheet = Sheet2

    'This will be the column that needs to be synced
    syncCol = 1
    
    'Check whether the cell that was changed in Sheet1 lies in Column 1
    If Target.Column = syncCol Then
        'If so, get the address of the cell that changed
        'And make the same change in corresponding cell in Sheet2
        targetSheet.Range(Target.Address) = sourceSheet.Range(Target.Address)
    End If

End Sub

 

Example 4: Synchronize data between two ranges

Let us look how to sync two ranges in this example. We will use Application.Intersect method to determine whether the cell that changed lies within the range to be synced. This returns the intersecting range of the specified ranges. And if there is no intersection (i.e the cell does not fall in the range), the return value is empty. Thus, we equate it with Nothing in our if condition.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim sourceSheet As Worksheet, targetSheet As Worksheet
    Dim syncRange As String
    Dim isInRange

    'Set the source and target sheets here
    Set sourceSheet = Sheet1
    Set targetSheet = Sheet2

    'This will be the column that needs to be synced
    syncRange = "A1:C5"
    
    'Check if the modified cell lies within the range to be synced
    Set isInRange = Application.Intersect(Target, Range(syncRange))
    
    
    If isInRange Is Nothing Then
        'Do nothing if the cell falls outside the range
    Else
        'Else sync the cell contents
        targetSheet.Range(Target.Address) = sourceSheet.Range(Target.Address)
    End If


End Sub

 

Thus, we have seen how to sync data between 2 sheets in Excel. Throughout the article we have assumed that the source data needs be synced at the corresponding cell in the target sheet. However, if you want to sync say, cell A1 in source to cell B2 in target, you can easily modify the code to something like this:

        targetSheet.Range("B2") = sourceSheet.Range("A1")

 

Secondly, the source and the target sheet can be same, like seen below

        sourceSheet.Range("B2") = sourceSheet.Range("A1")

 

For further reading on what is range.cells, refer to the article: What is VBA Range.Cells and How Do You Use It?

Leave a Reply

Your email address will not be published. Required fields are marked *