How can I automate populating an array using a named range on a specific worksheet?

The named range is SheetNames, on a worksheet named Consolidation, cell B3 to B101 and the array is Sheets(Array("Sheet1", "Sheet2")).Select:

Sub Insert_Formula()
    Dim ws As Worksheet
    Application.ScreenUpdating = False
    Sheets(Array("Sheet1", "Sheet2")).Select
    
    For Each ws In ActiveWindow.SelectedSheets
        ws.Range("F3:F50").Formula = "=SUMIFS(Jul!$K:$K,Jul!$H:$H,$C$1,Jul!$J:$J,$C3)"
    Next ws
  
    Application.ScreenUpdating = True 
End Sub

Solution

Hopefully this answers your question. If you are looking to iterate through every sheet in your workbook, instead of explicitly calling out their names you can just use

Sub Insert_Formula() 

Application.ScreenUpdating = false

For Each ws In ActiveWorkbook.Sheets
    ws.Range("F3:F50").Formula = "=SUMIFS(Jul!$K:$K,Jul!$H:$H,$C$1,Jul!$J:$J,$C3)"
Next

Application.ScreenUpdating = True 

End Sub

Alternatively if you want to use the specific sheet name you specified with the worksheet names you can try this

Sub Insert_Formula()

    For Each cell In Range("sheetNames")
        Sheets(cell.Text).Range("F3:F50").Formula = "=SUMIFS(Jul!$K:$K,Jul!$H:$H,$C$1,Jul!$J:$J,$C3)"
    Next

End Sub