Dim sSH   as Worksheet
Dim AWB  as Workbook

Set AWB = Activeworkbook
Set sSH = Sheets(1)
I used the macro recorder to see how Excel did it itself, and then used that to work out the error in my code....

This is now working:

Cell.Offset(0, 3) = "=SUMIF('[" & AWB.Name & "]Data'!$A$8:A" & lastRow & ",A2,'[" & AWB.Name & "]Data'!$G$8:G" & lastRow & ")"
I'd still like (for other applications) to know whether or not using "sSH" instead of "Data!" (the actual sheet name) is possible, but I haven't gotten it to work yet. This statement works however, so for now it's good enough