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?