In Excel, you may often work with multiple worksheets or workbooks while managing data. Sometimes, you need to sync data between two or more worksheets. Whether you’re using templates, repeating headers, or shared report values, Excel does not offer a built-in feature to link a selected range across multiple sheets directly. multiple worksheets or workbooks sync data templates headers report values not link a selected range In this tutorial, I’ve added the two methods, including using: VBA Code to Sync Worksheets.
Sync the Worksheets values using the Select All Sheets option. VBA Code to Sync Worksheets. Sync the Worksheets values using the Select All Sheets option. Method 1: Sync Worksheet Values Using the Select All Sheets Option Firstly, you need to select a range of cell values in the current active worksheet that you want to select in all worksheets. Firstly, you need to select a range of cell values in the current active worksheet that you want to select in all worksheets. As shown in the above image, I’ve chosen the cell values range between A2:B6
Then you need to right click on the worksheet tab and choose the Select All Sheets option from the list of menu. As shown in the above image, I’ve chosen the cell values range between A2:B6 A2:B6 Then you need to right click on the worksheet tab and choose the Select All Sheets option from the list of menu. worksheet tab Select All Sheets Now, all the values of the selected cell range have been copied and synced across all worksheets in the workbook. Now, all the values of the selected cell range have been copied and synced across all worksheets in the workbook. values cell range synced across all worksheets This image displays the range of cell values that have been selected across all worksheets in a workbook. That’s it; this is how you can sync values in a range of cells in the workbook using the Select All Sheets option in Excel. Note: Using the “Select All Sheets” option in Excel lets you apply the same selected range to all worksheets at once. However, this only syncs the selection itself, not the visible position of the selection on the screen. To view the selected range in the same spot on each sheet, you’ll need to manually scroll each worksheet. Note: Using the “Select All Sheets” option in Excel lets you apply the same selected range to all worksheets at once. However, this only syncs the selection itself, not the visible position of the selection on the screen. To view the selected range in the same spot on each sheet, you’ll need to manually scroll each worksheet. Note: Method 2: Sync Worksheet Values Using VBA Code: This is the alternative method to do. You might need to use the VBA code to sync the worksheet values. Using VBA code in Excel, you can easily make all worksheets select the same range and also ensure that the selected range is visible in the same position of the window across all sheets. You can use the keyboard shortcut ALT + F11 to launch the VBA Editor in Excel. VBA code ALT F11 Firstly, you need to select the range of cell values (IMPORTANT) as shown in the image. Firstly, you need to select the range of cell values (IMPORTANT) as shown in the image. IMPORTANT Then, you need to use the keyboard shortcut ALT + F11 to launch the VBA Code editor. Then, you need to use the keyboard shortcut ALT + F11 to launch the VBA Code editor. Now, in the Insert option located at the top of the Menu bar, you need to choose the Module option.
An editor window will appear on your screen, where you need to paste the code below. Now, in the Insert option located at the top of the Menu bar, you need to choose the Module option. Insert Module An editor window will appear on your screen, where you need to paste the code below. Sub SynchSheets()
'Update 20130912
Dim WorkShts As Worksheet
Dim sht As Worksheet
Dim Top As Long
Dim Left As Long
Dim RngAddress As String
Application.ScreenUpdating = False
Set WorkShts = Application.ActiveSheet
Top = Application.ActiveWindow.ScrollRow
Left = Application.ActiveWindow.ScrollColumn
RngAddress = Application.ActiveWindow.RangeSelection.Address
For Each sht In Application.Worksheets
    If sht.Visible Then
        sht.Activate
        sht.Range(RngAddress).Select
        ActiveWindow.ScrollRow = Top
        ActiveWindow.ScrollColumn = Left
    End If
Next sht
WorkShts.Activate
Application.ScreenUpdating = True
End Sub Sub SynchSheets()
'Update 20130912
Dim WorkShts As Worksheet
Dim sht As Worksheet
Dim Top As Long
Dim Left As Long
Dim RngAddress As String
Application.ScreenUpdating = False
Set WorkShts = Application.ActiveSheet
Top = Application.ActiveWindow.ScrollRow
Left = Application.ActiveWindow.ScrollColumn
RngAddress = Application.ActiveWindow.RangeSelection.Address
For Each sht In Application.Worksheets
    If sht.Visible Then
        sht.Activate
        sht.Range(RngAddress).Select
        ActiveWindow.ScrollRow = Top
        ActiveWindow.ScrollColumn = Left
    End If
Next sht
WorkShts.Activate
Application.ScreenUpdating = True
End Sub Now, in the Menu bar, you may see the option “Run“, click on it, and choose the Run Sub/Userform F5 option to execute the VBA code. Alternatively, you can use the keyboard shortcut F5 to run the command. Now, in the Menu bar, you may see the option “Run“, click on it, and choose the Run Sub/Userform F5 option to execute the VBA code. Alternatively, you can use the keyboard shortcut F5 to run the command. Run Run Sub/Userform F5 Get back to the worksheet. There, you can see that the selected range of cells has been synchronized with all the worksheets in the workbook. Get back to the worksheet. There, you can see that the selected range of cells has been synchronized with all the worksheets in the workbook. That’s it. This tutorial was originally published on How to Sync Selected Range Across All Worksheets in Excel? How to Sync Selected Range Across All Worksheets in Excel

This story will praise and/or roast a product, company, service, game, or anything else people like to review on the Internet.

Range

Reverse Excel Cell Signs With the Paste Special & VBA in Easy Steps

How to Convert Radians to Degrees in Excel Using Simple Formulas

View Website Here

Read My Stories

Too Long; Didn't Read

Make resilience your competitive advantage

Sync Excel Worksheets With VBA & the 'Select All Sheets' Method

About Author

Comments

TOPICS

THIS ARTICLE WAS FEATURED IN

Related Stories

Untitled Story

Excel Formulas for Assigning Categories by Value Range Using Functions!

4 Data Transformations Made Spreadsheet-Easy

5 Excel Features That Can Grow Your Net Worth in No Time

5 Ways to Add Security to Excel & PDF with .NET Document APIs

7 Essential Things in Excel That You Definitely Need to Master

Excel Formulas for Assigning Categories by Value Range Using Functions!

4 Data Transformations Made Spreadsheet-Easy

5 Excel Features That Can Grow Your Net Worth in No Time

5 Ways to Add Security to Excel & PDF with .NET Document APIs

7 Essential Things in Excel That You Definitely Need to Master

Light-Mode

Classic

Newspaper

Dark-Mode

Neon Noir

Minty

HN StartUps