Condense code

there's no need to select a sheet:

For Each ws In Worksheets
  If ws.Name <> "Invoice" Then sheets("Invoice").Range("J22")="=" & ws.name & "!C[17]"
next

But this overwrites the same cell 7 times (?)

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.

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