How to sync two worksheets

Copper Contributor

Hi, I am looking for a way to save myself repeating the same task 100x by syncing several worksheets to the source worksheet, so that if I delete or add column's and rows the same happens on the other worksheets. 

 

I know how to have cell values synced, I just need to know how to have row/column additions/deletions also synced it would save me a lot of time.

 

thank you in advance

1 Reply

@SCF0086 

If you want to sync not only cell values but also row/column additions/deletions across multiple worksheets, you can consider using Excel Tables (ListObjects). Excel Tables are dynamic ranges that automatically expand or contract as you add or delete rows and columns, making them a great choice for syncing data.

Here is a step-by-step guide:

  1. External Data Connections:
    • You can set up external data connections to pull data from a master worksheet into other worksheets.
    • Changes in the master worksheet can be updated in the connected worksheets.

Remember that features and capabilities can evolve with newer versions of Excel, and it's possible that Microsoft may introduce new features in subsequent releases. Always check the documentation or updates specific to your Excel version for the latest information.

 

Syncing Row/Column Additions and Deletions Using Excel Tables:

  1. Convert Your Data Range to an Excel Table:
    • Select the range of cells in your source worksheet.
    • Go to the "Insert" tab and click on "Table" (or press Ctrl + T).
    • Ensure that the "Create Table" dialog box correctly identifies the range and check the box if your table has headers.
  2. Name the Table:
    • With the table selected, go to the "Table Design" tab.
    • In the "Table Name" field, give your table a name.
  3. Repeat for Other Worksheets:
    • Copy the original table (Ctrl+C).
    • Go to another worksheet and paste the table (Ctrl+V).
    • Repeat this step for all worksheets you want to sync.
  4. Syncing Data:
    • If you make changes to the structure of the original table (add/delete rows/columns), those changes will automatically reflect in all other tables.

Important Considerations:

  • Table Name: Ensure each table has the same name on all worksheets. This is important for proper syncing.
  • Cell References: Formulas referring to the table on one sheet may need adjustments if the table name is not the same on the other sheets.
  • Compatibility: Excel Tables are available in Excel 2016 and later versions. If you are using an earlier version, you might consider using Named Ranges or a VBA solution.

 

VBA (Optional):

If you are comfortable with VBA, you can also create a more customized solution using VBA to handle the syncing process. However, using Excel Tables is often a simpler and user-friendly approach.

Here's a basic example of VBA code for syncing tables:

Vba Code is untested, please backup your file.

Sub SyncTables()
    Dim ws As Worksheet
    Dim originalTable As ListObject

    ' Set the original table
    Set originalTable = Worksheets("SourceSheet").ListObjects("TableName")

    ' Loop through other sheets
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> "SourceSheet" Then
            ' Delete existing table
            On Error Resume Next
            ws.ListObjects("TableName").Delete
            On Error GoTo 0

            ' Copy original table to other sheets
            originalTable.DataBodyRange.Copy Destination:=ws.Range("A1")

            ' Rename the table
            ws.ListObjects(1).Name = "TableName"
        End If
    Next ws
End Sub

Remember to replace "SourceSheet" and "TableName" with the actual names of your source sheet and table. Run this macro to sync the tables on all sheets. The text, steps and code were created with the help of AI.

 

My answers are voluntary and without guarantee!

 

Hope this will help you.

Was the answer useful? Mark as best response and Like it!

This will help all forum participants.