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")).SelectFor 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.
Answer
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
No comments:
Post a Comment