Hello, I am wondering if there is anything in Excel which would allow me to create identical input tables on different sheets and have that data drive an output table on another sheet. The inputs tables should be identical at all times and the user should be able to enter data on either.
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Multi-Sheet Input Table
- Thread starter Excel2021
- Start date
Kaper
Active Member
- Joined
- Mar 14, 2014
- Messages
- 482
- Office Version
- 365
- Platform
- Windows
You can write an event handler for SheetChange event in a Workbook. Assuming your 2 input sheets are: Sheet1, and Sheet2, and the output sheet is called just Output. Save the workbook in VBA enabled file (extension xlsm, xlsb, etc.)
Use such code in Thisworkbook module (see screenshot):
Use such code in Thisworkbook module (see screenshot):
VBA Code:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim shdest As Worksheet
If Sh.Name = "Sheet1" Then Set shdest = Sheets("Sheet2")
If Sh.Name = "Sheet2" Then Set shdest = Sheets("Sheet1")
If Not shdest Is Nothing Then
Application.EnableEvents = False
Target.Copy shdest.Range(Target.Address)
Target.Copy Sheets("Output").Range(Target.Address)
Application.EnableEvents = True
End If
End Sub
Upvote
0
