Populating an array with named range on a specific worksheet

Question

I am new to VBA and trying to automate populating an array using a named range on a specific worksheet. The array in question is in bold in below code. The named range is "SheetNames", on a worksheet named "Consolidation", cell B3 to B101.

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

Any help would be greatly appreciated.

Hi, welcome to SO. Just to be clear, what exactly isn't working? — GraphicsMuncher
GraphicsMuncher, the code is working but everytime I change the worksheet names, I have to manually copy-paste them into the array (e.g. "Sheet1", "Sheet2", etc.). I can automate this using a named range, but I don't know how to integrate it into the code. — Elle Ciel

Silenxor Silenxor · Accepted Answer · 2014-02-03T02:22:12

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

Populating an array with named range on a specific worksheet

1 Answers