Regardless of cell references, how would I make a sumifs formula reference across multiple sheets in VBA?
This is being built into a template and there would be a different number of sheets with different names each time it is run so I would be not be able to reference the sheets.
This is how it was done in full:
My original formula was
"=SUMPRODUCT(SUMIF(INDIRECT(" '"&Invoices&"'!"&"A2006:A3005"),A3,INDIRECT("'"&Invoices&"'!"&"B2006:B3005")))"
this worked when putting straight into a cell but as you can see, when adding it to VBA it reads it as a comment. To fix this, every time you use a " you need to add extra " as shown below (apart from before the "= at the start and after the )" at the end of the formula).
*****'list all the sheet names in cell AI1 of the sheet summary*****
For i = 1 To Sheets.Count
Sheets("Summary").Range("AI1")(i, 1).Value = Sheets(i).Name
Next i
***'clear the first 3 entries in AI as i didnt need the first three sheet names***
Sheets("Summary").Select
Sheets("Summary").Range("AI1:AI3").Clear
***'select the first sheet name, which is in AI4 as we cleard the first 3 to the last used cell, e.g Ctrl.Shift.down***
Sheets("Summary").Activate
Sheets("summary").Range(ActiveSheet.Range("AI4"), ActiveSheet.Range("AI4").End(xlDown)).Select
***' Name the range invoices***
Selection.Name = "Invoices"
' ***Formula to do a sumIf looping all the shets in the named range Invoices***
Sheets("summary").Range("B3").Formula = "=SUMPRODUCT(SUMIF(INDIRECT(""'""&Invoices&""'!$A$2006:$A$3005""),$A3,INDIRECT(""'""&Invoices&""'!B$2006:B$3005"")))"