Share Data between multiple sheets

By: Digital Deeper

Published: 16/06/2022

It's common to feel the need to transfer data from one worksheet to another when dealing with many sheets in Excel. There are several simple and quick approaches to achieve this goal. With appropriate examples and simple explanations, you'll learn how to move data to another spreadsheet in this article.

Using the Ctrl key to group worksheets is a simpler method to do this. When worksheets are grouped together, everything you do to one impacts all of the others.

Use Across Worksheet Option:

This method uses the worksheet group feature. This feature can transfer all the highlighted data from one sheet to all the selected or targeted sheets in a matter of seconds.

Start Microsoft Excel and you will see a blank workbook. Click on Insert Worksheet option from the bottom left corner of the excel window.

Write the content and select all the content which you want to transfer to targeted work sheet.

With ctrl + click, select the sheets that you want to input the content in. If you want to select all the sheets at once right click and use “Select all Sheets” option. Now you have created a group of multiple sheets.
Navigate to Home > Fill button in toolbar and select Across Worksheets option from the drop-down menu.

A new window will pop up; select any one fill option among these three.

Now all of your content will be seen in selected sheets.

Tip: Right click on sheet and select ungroup sheets

Create a group of multiple worksheets:

To utilize this method first you have to create a worksheet group. Consider given steps:

Drag the mouse to any sheet at the bottom left corner, right click and use ‘Select all Sheets’ option.
This feature creates a worksheet group in a workbook.
After all this you can input data in any sheet and it will appear in all the other worksheets.
To ungroup worksheets, simply double-click any worksheet in the workbook or right click and select ungroup sheets.

Copy and paste worksheet:

This method only needed one worksheet to be created and all the data can be shared between multiple sheets.

Insert your data into worksheet and arrange it according to your need.

Now right click this worksheet.

In the pop-up list choose the option “Move or Copy”.

Check the ‘Create a copy’ checkbox in the Move or copy window.

New excel-sheet is created and it contains all the information as the previous one. You can change the name of worksheet.

Use VBA Macro to transfer data automatically to any worksheet:

In Excel, you can enter the same data into several worksheets without retyping or copying and pasting the text into each one. One advance way to do this is to use VBA macro to copy and paste the text into the other worksheets.

Create a worksheet with name Sheet1 and enter your data.
Create another worksheet by simply pressing the Insert Worksheet button.
Press “Alt + F11” button a new window will popup.
Right click the “VBA Project”, select insert option and click Module button.
Input this code into the module window:
```
Sub CopyPaste()
Worksheets("Sheet1").Range("A1:F10").Copy Worksheets("Sheet2").Range("A1")
End Sub
```
Note: Select the range (Range (“A1:F10”)) according to your requirements also you can put any name of your sheets. Range (“A1:F10”) shows that the data within A1 (Column: A, Row: 1) to F10 (Column: F, Row: 10), will be fetched and copied to the A1 (Column: A, Row: 1) of Sheet2.
Press the button “F5” on the keyboard. Therefore, the same contents will also appear in the other worksheets.

With help of all these methods you can share data of pivot table and protected worksheet easily to multiple worksheets.