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.
Step 2: After the Group has finished entering data in any sheet.
Step 3: Results in other sheets:
Sheet 5:
If you want to remove certain sheets from a group, do the following: Right-click the sheet name -> Select UnGroup Sheets .
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 .
Step 2: A dialog box appears that gives the name of selected data area -> OK . Note the name of the selection.
After naming the selection:
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:
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.
Results in other sheets:
Sheet 4:
Sheet1:
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.
Please note that it is up to you to request the location of data entry:
Step 2: Press the key combination Alt + F11 to return to Sheet5 to enter new data.
Results in Sheet 1:
Results in Sheet 3:
Above are the methods to help you input and save time and effort.
Good luck!