Question about populating cells with formulas in a dynamically updating index

[ZoopZoop]

I'm using a VBA code (that I found and modified by lurking here, kudos!) to create a dynamic index that I then use 15 different Indirect formulas to populate cells with values that create a summary. Basically each cel from B2-P2 is populated with some variation of: =IF(ISBLANK(A2),"",INDIRECT(A2&"!H1"))


I've tried using pivots, but it doesn't get me the formatting that I need, so I've settled on using formulas. Unfortunately, this means that whenever a sheet is added, I then need to manually copy the formula from the above cel by hand. Any suggestions on how to further automate the process? Perhaps something that says if row # = >2 and row = B-P then copy formula from cel above it? I feel like I'm circling the answer but now quite expressing it correctly.


For reference, here is how I am creating the index in the first place. It grabs all the worksheet names except two specifically named ones and anything else I hide and then populates those names in Cels A2 onward.

Private Sub Worksheet_Activate()
Dim wSheet As Worksheet
Dim n As Integer
Dim calcState As Long, scrUpdateState As Long


calcState = Application.Calculation
Application.Calculation = xlCalculationManual
scrUpdateState = Application.ScreenUpdating
Application.ScreenUpdating = False


n = 1


    With Me
        .Columns(1).ClearContents
        .Cells(1, 1) = "Inv #"
        .Cells(1, 1).Name = "Index"
    End With
    
    For Each wSheet In Worksheets
        If wSheet.Name <> Me.Name And wSheet.Name <> "100000" And wSheet.Name <> "999999" And wSheet.Visible = xlSheetVisible Then
            n = n + 1
                With wSheet
                    .Range("O1").Name = "Start_" & wSheet.Index
                     .Hyperlinks.Add anchor:=.Range("O1"), Address:="", _
                    SubAddress:="Index", TextToDisplay:="Back to Index"
                End With
                
                Me.Hyperlinks.Add anchor:=Me.Cells(n, 1), Address:="", _
                SubAddress:="Start_" & wSheet.Index, TextToDisplay:=wSheet.Name
        End If
    Next wSheet
    
Application.Calculation = calcState
Application.ScreenUpdating = scrUpdateState
End Sub

 

[Snakehips]

@ZoopZoop
Maybe like ...

Private Sub Worksheet_Activate()Dim wSheet As Worksheet
Dim n As Integer
Dim calcState As Long, scrUpdateState As Long




calcState = Application.Calculation
Application.Calculation = xlCalculationManual
scrUpdateState = Application.ScreenUpdating
Application.ScreenUpdating = False




n = 1




    With Me
        .Columns(1).ClearContents
        .Cells(1, 1) = "Inv #"
        .Cells(1, 1).Name = "Index"
    End With
    
    For Each wSheet In Worksheets
        If wSheet.Name <> Me.Name And wSheet.Name <> "100000" And wSheet.Name <> "999999" And wSheet.Visible = xlSheetVisible Then
            n = n + 1
                With wSheet
                    .Range("O1").Name = "Start_" & wSheet.Index
                     .Hyperlinks.Add anchor:=.Range("O1"), Address:="", _
                    SubAddress:="Index", TextToDisplay:="Back to Index"
                End With
                
                Me.Hyperlinks.Add anchor:=Me.Cells(n, 1), Address:="", _
                SubAddress:="Start_" & wSheet.Index, TextToDisplay:=wSheet.Name
                
[COLOR=#ff0000]                Select Case n[/COLOR]
[COLOR=#ff0000]                    Case 2[/COLOR]
[COLOR=#ff0000]                    '****Specify formulas for row 2 columns B to P. eg...[/COLOR]
[COLOR=#ff0000]                    Me.Cells(n, 2).Formula = "=IF(ISBLANK($A2),"""",INDIRECT($A2&""!H1""))"[/COLOR]
[COLOR=#ff0000]                    Me.Cells(n, 3).Formula = "=IF(ISBLANK($A2),"""",INDIRECT($A2&""!J1""))"[/COLOR]
[COLOR=#ff0000]                    Me.Cells(n, 4).Formula = "=IF(ISBLANK($A2),"""",INDIRECT($A2&""!D5""))"[/COLOR]
[COLOR=#ff0000]                    Case Else[/COLOR]
[COLOR=#ff0000]                    Range("B" & n - 1 & ":P" & n - 1).AutoFill Destination:=Range("B" & n - 1 & ":P" & n), Type:=xlFillDefault[/COLOR]
[COLOR=#ff0000]                End Select[/COLOR]
        End If
    Next wSheet
    
Application.Calculation = calcState
Application.ScreenUpdating = scrUpdateState
End Sub

Not knowing the nature of the root formulas in row 2 so may need to code them all then other rows will copy.

Hope that helps.

 

[ZoopZoop]

@ZoopZoop
Maybe like ...

[COLOR=#ff0000]                Select Case n[/COLOR]
[COLOR=#ff0000]                    Case 2[/COLOR]
[COLOR=#ff0000]                    '****Specify formulas for row 2 columns B to P. eg...[/COLOR]
[COLOR=#ff0000]                    Me.Cells(n, 2).Formula = "=IF(ISBLANK($A2),"""",INDIRECT($A2&""!H1""))"[/COLOR]
[COLOR=#ff0000]                    Me.Cells(n, 3).Formula = "=IF(ISBLANK($A2),"""",INDIRECT($A2&""!J1""))"[/COLOR]
[COLOR=#ff0000]                    Me.Cells(n, 4).Formula = "=IF(ISBLANK($A2),"""",INDIRECT($A2&""!D5""))"[/COLOR]
[COLOR=#ff0000]                    Case Else[/COLOR]
[COLOR=#ff0000]                    Range("B" & n - 1 & ":P" & n - 1).AutoFill Destination:=Range("B" & n - 1 & ":P" & n), Type:=xlFillDefault[/COLOR]
[COLOR=#ff0000]                End Select[/COLOR]

Not knowing the nature of the root formulas in row 2 so may need to code them all then other rows will copy.

Hope that helps.

Okay, I see what's happening here. I'm setting the formulas once in the script and it'll populate from then on. I did a test case and it worked! Thank you, I'll drop all 15 formulas in here, add the "quote" formatting and let it rip!