Dynamic updating across multiple sheets

[kenny9002]

I need help with how to dynamically update multiple Excel analysis sheets (22 sheets) from a Master Sheet (dynamic table) – all in the same workbook.
<o></o>
The Master Sheet<o></o>
The Master Sheet (which is a dynamic table) currently has about 900 rows and 12 columns. On a weekly basis, when I want to add new data, I press TAB and a new row is added for me to enter new data across the columns.
<o></o>
The Analysis Sheets (22 of them)
1. Each of the 22 analysis sheets also currently has 900 rows but 34 columns.

2. All the analysis sheets are identical in terms of no. of rows, columns and formula in each cell.

3. The first four columns of each analysis sheet is the same as the first 4 columns of the Master Sheet. The said first 4 columns have the titles - No., Day, Mth, Year.

4. The other remaining 30 columns are filled, each, with HLOOKUP formula. The HLOOKUP formula picks up data from the 8 columns in the Master Sheet and analyses them across the 30 columns of each Analysis sheet.
<o></o>
Everything as has been set up is working OK for now.
<o></o>
As indicated above, the Master Sheet is a dynamic table and when I press TAB, a new row is created, and I am able to add new data. However, my problem is this: In order to update the 15 analysis sheets to be able to pick up the new row of data from the Master Sheet, I will have to click on each analysis sheet and type in the formula onto a new row and reference it to the Master Sheet and do that manually for each of the 22 analysis sheets.
<o></o>
Can Excel do this for me? Is it possible to also make the 22 analysis sheets dynamic so that when a new row of data is added to the Master Sheet, each of the analysis sheets, will, by pressing a TAB or something, be made to automatically insert a row with the relevant formula and be able to pick the new data from the Master Sheet and analyse the same way as done with data entered earlier.
<o></o>
Please, I’ll be very grateful for any help.
<o></o>
Kenny

 

[Trevor G]

Kenny you will need some VBA to do this for you using a loop I suspect, Something like this on a click of a button the Master sheet once you have everything created the entry.

Sub current1()
Dim ws As Worksheet
Application.ScreenUpdating = False
For Each ws In Worksheets
If ws.Name <> "Master" Then
ws.Activate
'do what it needs to (which column)
Next ws
Application.ScreenUpdating = True
MsgBox "New data has been added", vbInformation, "Kenny"
End Sub

 

[kenny9002]

Thanks Trevor for your kind response and help. I was thinking that I'd be able to achieve that result with some Excel formula rather than resorting to VB. I appreciate your help.

However, if any member of this esteemed forum has an Excel-formula solution to my problem, I would be very grateful.

Again, Trevor, thanks so much for your help.

Kenny