Method to enter data simultaneously into multiple sheets

The following article details how to enter data simultaneously into multiple sheets.

There are 2 ways to enter data simultaneously into multiple sheets.

Method 1: Group the sheets into groups using the Ctrl key.

Method 2: Use VBA to enter data.

1. Group the sheets into groups using the Ctrl key

Step 1: Hold down Ctrl and click on the names of the sheets to enter data simultaneously. After selecting all the grouped sheets to form a Group , this is shown in the file name.

Method to enter data simultaneously into multiple sheets Picture 1

Step 2: After the Group has finished entering data in any sheet.

Method to enter data simultaneously into multiple sheets Picture 2

Step 3: Results in other sheets:

Method to enter data simultaneously into multiple sheets Picture 3

Sheet 5:

Method to enter data simultaneously into multiple sheets Picture 4

If you want to remove certain sheets from a group, do the following: Right-click the sheet name -> Select UnGroup Sheets .

Method to enter data simultaneously into multiple sheets Picture 5

Note: The disadvantage of this method is that you cannot have specific options for any sheet input data to be filled in other sheets. You should use VBA to enter data.

2. Use VBA to enter data

Step 1: Open Excel, create 5 sheets you want to import data. Here I want to enter the original data in sheet 5. At sheet5 create the data range you want to import (other sheets only enter the same data in this selection) -> Right-click and choose Define Name .

Method to enter data simultaneously into multiple sheets Picture 6

Step 2: A dialog box appears that gives the name of selected data area -> OK . Note the name of the selection.

Method to enter data simultaneously into multiple sheets Picture 7

After naming the selection:

Method to enter data simultaneously into multiple sheets Picture 8

Step 3: Press the key combination Alt + F11 . VBA window appears, select Sheet5 -> Select the WorkChange Selection Event of Worksheet and enter the command as shown below:

Method to enter data simultaneously into multiple sheets Picture 9

Code:

// Event SelectionChange. Private Sub Worksheet_SelectionChange (ByVal Target As Range) If Not Intersect (Range ("MyRank"), Target) Is Nothing Then Sheets (Array ("Sheet5", "Sheet4", "Sheet3", "Sheet2", "Sheet1")) .Select Else Me.Select End If End Sub // Event Change. Private Sub worksheet_Change (ByVal Target As Range) If Not Intersect (Range ("MyRank"), Target) Is Nothing Then With Range ("MyRank"). Copy Destination: = Sheets ("Sheet3"). Range ("A5") .Copy Destination: = Sheets ("Sheet1"). Range ("E5") End With End If End Sub

Note: You choose the Sheet that input data then the command to write that sheet first. As an example here, Sheet5.

Step 4: Press Alt + F11 to return to enter data in sheet5.

Method to enter data simultaneously into multiple sheets Picture 10

Results in other sheets:

Sheet 4:

Method to enter data simultaneously into multiple sheets Picture 11

Sheet1:

Method to enter data simultaneously into multiple sheets Picture 12

Note: If you enter data in Sheet 5 outside the selection named MyRank, the data cannot be entered into the other sheets.

If you want to enter data in different locations in the other sheets, you can do the following:

Step 1: Press the key combination Alt + F11 VBA window appears select Sheet5 -> select events Change of Worksheet -> enter the following code.

Method to enter data simultaneously into multiple sheets Picture 13

Please note that it is up to you to request the location of data entry:

Method to enter data simultaneously into multiple sheets Picture 14

Step 2: Press the key combination Alt + F11 to return to Sheet5 to enter new data.

Method to enter data simultaneously into multiple sheets Picture 15

Results in Sheet 1:

Method to enter data simultaneously into multiple sheets Picture 16

Results in Sheet 3:

Method to enter data simultaneously into multiple sheets Picture 17

Above are the methods to help you input and save time and effort.

Good luck!