Inserting a formula into a range of cells using VBA

Hi

I am trying to convert this formula so that I can use a macro to insert it into a range of cells...

=SUMPRODUCT(SUMIF(INDIRECT("'"&TabNames&"'!A2:A1000"),A2,INDIRECT("'"&TabNames&"'!E2:D25")))

I can't get it to work...mainly because of the single and double quotes getting me confused....

Can anyone suggest?

This is the point I'm at with no luck:

With Range(Cells(2, 5), Cells(lastrow, 5))
     .Formula = "=SUMPRODUCT(SUMIF(INDIRECT(""&" & "'" & "&TabNames&"&"'"&"!A1:A1000")," & Range("A2").Address(ROWABSOLUTE:=False) & ",INDIRECT("'"&TabNames&" '!E1:E1000")))"
   
End With