Condense code

Even better snb, thanks for your suggestion.

I'm using the following now (ADV sheets are a special case that need a bit extra info, and slightly amended formula to SUM):

For Each ws In Worksheets
        If ws.Name <> "Invoice" Then
            Select Case ws.Name
            Case "ADV"
            Sheets("Invoice").Range("J22") = "=SUM(" & ws.Name & "!C[17])"
            Range("B2", Range("A65536").End(xlUp).Offset(, 1)).FormulaR1C1 = "=VLOOKUP(RC[-1],'[Timesheet import.xlsm]Completions'!C1:C2,2,0)"
            Range("B2", Range("A65536").End(xlUp).Offset(, 1)) = Range("B2", Range("A65536").End(xlUp).Offset(, 1)).Value
            Case Else
            Sheets("Invoice").Range("J22") = "=SUM(" & ws.Name & "!C[17])"
            End Select
        End If
    Next ws

-edit: The workbook will consist of 1 of the following 7 combinations of sheets:

"Invoice" and "Completes"
"Invoice" and "Aborts"
"Invoice" and ...
"Invoice" and ...
"Invoice" and ...
"Invoice" and ...
"Invoice" and ...

So the formula is only entered once on "Invoice" to sum the backing data.

Again, many thanks both for your help.