Using a Master Sheet to auto populate other sheets automatically

[dukewins]

I am like owing to create a single file with approximately 100 sheets. Each of the sheets will have text, information and formulas from data on a master sheet.

my question is, how can I get the sheets to auto populate by entering master sheet data?

withiut having to manually adjust the formulas for each sheet, down 1 row then another row, then another

 

[BrianJN1]

First question:
How will the Master Sheet data be added, manually or via some kind of form?
Secondly, what elements of the Master Sheet would be needed on each of your c.100 sheets?

I'm imagining that a set of c.100 Macros could be written which generates formulae appropriate for each sheet. Those formulae would be embedded in the sheet and would look directly back to a specific location on the Master Sheet.

Here is something that I have used to draw, dynamically, from a "master" sheet. A macro has the bold formulae as shown within it. I've shown an example of what that macro creates within a cell. The first formula is used to create a column of names. The second is used to draw upon data across columns and is against the "name" of the first formula.

Cells(x, y).FormulaR1C1 = "=INDIRECT(ADDRESS(" & x & "," & y + 3 & ",1,1,""Master Sheet""))"
=INDIRECT(ADDRESS(7,4,1,1,"Master Sheet"))

Cells(x, y).FormulaR1C1 = "=IF(INDIRECT(ADDRESS(" & x & "," & y + 7 & ",1,1,""Master Sheet""))<>"""",INDIRECT(ADDRESS(" & x & "," & y + 7 & ",1,1,""Master Sheet"")),"""")"
=IF(INDIRECT(ADDRESS(8,22,1,1,"Master Sheet"))<>"",INDIRECT(ADDRESS(8,22,1,1,"Master Sheet")),"")

I'll attempt to advise further if this is appropriate to your needs. It will be a big exercise, but then it looks like your project needs some big data.

 

[dukewins]

That's pretty advanced for me. Let me explain further.
i have a master sheet with universal information, such as a rate. The rate will
be linked back for all sheets. Also on the master is a row
of information - call it row 10. Sheet 2 will have links
to row 10 on the master, such as A10, B10, C10, etc.
now I eat to create Sheet 3 and populate it with A11, B11,
C11, etc.

is there an auto-populate way, or do I just have to copy sheet
2, rename it sheet 3 and manually change the links from A10
to A11, etc.

 

[BrianJN1]

Are you suggesting to me that Sheet 1 Row 10 has formulae which are to be copied to Sheet 2 Row 11, Sheet 3 Row 12, Sheet 4 Row 13, etc?
If so will the Sheet Names change? You initially indicated that this file could have up to 100 sheets.
I'm understanding that each cell on
Sheet 2 Row 11 goes =A10 =B10 = C10 etc
Sheet 2 Row 12 goes =A10 =B10 = C10 etc

 

[dukewins]

Master sheet has data in each row
sheet 1 has data that calls back to A10, B10, C10
i copy sheet 1 and name it sheet 2
i want sheet 2 to call back to data in A11, B11, C11

the goal is to have each of the 100 sheets call back to a different row of master sheet data without manual changes

 

[BrianJN1]

So somewhere we have Master Sheet with range(A1:C100) which will ultimately be filled with data.
You want Sheet 1 to show Master Sheet A1:C10, Sheet 2 is Master Sheet A1:C11, Sheet 3 is Master Sheet A1:C12.....

I gather that the sheets will be named Sheet 1, Sheet 2, Sheet 3, ....

I suggest that you look at my two proposals in small workbooks because if you choose to delete, eg, Sheet 1 that name will no longer be created in the current workbook.
(There is a way to name your sheets as you go but I'm not conversant with that.)

Ok. My proposals:
1. The first macro selects a range of data from Master and copies that data to each new sheet with a new row added each time.
2. Create a sheet called Master and a sheet called Formulae. In Formula select A1 and type =Master!A1. In B1 type =Master!B1 and in C1 type Master!C1.
Now select A1:C1 and drag down to row 110. That now gives a 'master list' of formulae referring to "Master". When the second macro is run it will select an appropriate range of formulae and add then increasingly to each new sheet. Type some values into Master and check them across the newly created sheets.

These are my codes:

[B]Sub Mirror_Master_Plus1()[/B]
'
' Copy DATA to new sheet
'

'
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim x As Long
    Sheets("Master").Select
    For x = 1 To 10
    Range("Master!A1:C" & 9 + x).Copy
    Sheets.Add After:=ActiveSheet
        Range("A1").Select
        ActiveSheet.Paste
    Next
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
End Sub
________________________________________________________________________________
________________________________________________________________________________

[B]Sub Mirror_Master_Plus2()[/B]
'
' Copy FORMULAE to new sheet
'

'
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim x As Long
    Sheets("Formulae").Select
    For x = 1 To 5
    Range("'Formulae'!A1:C" & 9 + x).Copy
    Sheets.Add After:=ActiveSheet
        Range("A1").Select
        ActiveSheet.Paste
    Next
    Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

If the second code is the one that you want then change the 5 to 100.
I'm guessing that to create 100 sheets is going to take some time so I've added some lines which should reduce that.
That said, I'm sure there is someone who can suggest a speedier, or even more efficient method to do this should either be of your choice.

Please refer back.

 

[dukewins]

does the code change if I want each sheet to have static master data, such as cells A1, B1, C1, but then also individual data sets - sheet 1 would be A10:J10, sheet 2 would be A11:J11, sheet 3 would be A12:J12,

 

[BrianJN1]

The code on Sheet 1 Col A will read down as = Master!A1, =Master!A2 .... to =Master!A10
On Sheet 2 Col A will read down exactly the same but with the addition of =Master!A11.

Since you want to go across to Col J drag the formulae in Formula across to Col J and change This line:
Range("'Formulae'!A1:C" & 9 + x).Copy
to
Range("'Formulae'!A1:J" & 9 + x).Copy

I did mention in my previous post that you need to change the 5 to 100.

As to the data. Data recorded on Master will be static. The data on each of the sheets will be exactly as on Master for the range assigned to the sheet.

 

[dukewins]

Thank you. I will try that macro and advise