Selected Answer
Hello Sunil,
Actually, your code is just fine but you forgot to consider where you put it. You placed it in the code module of a worksheet and from there you can only manipulate the ActiveSheet. Your effort to write to all sheets in the workbook is bound to fail for this reason.
First. One would think that the ActiveSheet, in case of a change event, can only be the sheet that triggered the event. Unfortunately that isn't true. Your own code writes to several sheets and thereby triggers change events on several sheets, one or none of which can or must be the ActiveSheet. Therefore I recommend this rule.
- Never use an event procedure to write to a sheet other than the one that triggered the event.
- Even then, never omit specifying the sheet you are writing to unless you are very sure that the event will not be triggered by an event from another sheet.
Second. Instead of using the event procedure to manipulate all the sheets let the event only call a procedure in a standard code module, and let that procedure do the manipulating. Just pass the Target argument to the executing procedure. Make sure to turn off application events for reasons mentioned above.
Although your code probably didn't need it, I made a few changes. One of them concerns the definition of the Trigger range. The range is E5:G9 but you declared a range B4:I9. This imprecision doesn't immediately affect the functioning of your code but it's sloppy programming, even more so because your range "InputTable" actually is a dulicate of the named range "Table1" created by the table itself. Table1 covers the table's DataBodyRange, meaning excluding headers and totals. So, when you created the InputTable range you really wanted the already existing Table1 range (next time give a meaningful name to the table which the range will adopt automatically) but what you needed is Range(.Columns(4), .Columns(6)) of Range("Table1").
I also made some changes to your cell formatting on the Input sheet. In the attached workbook look for the use of the Indent which works for both left and right aligned cells.
