Condense code

I am currently learning to condense code by turning several macros that are similar into one that I can use for all.

The below is the last bit that I need to change to have a single macro instead of 7.

At this point, I am copying the ActiveSheet and a sheet called "Invoice" to a new workbook, then adding the formula to the sheet called "Invoice":

Sub InvComps()

    Sheets(Array("Invoice", ActiveSheet.Name)).Copy
    Range("J22").FormulaR1C1 = "=SUM(Complete!C[17])"
    
End Sub

Sub InvAborts()

    Sheets(Array("Invoice", ActiveSheet.Name)).Copy
    Range("J22").FormulaR1C1 = "=SUM(Aborts!C[17])"
    
End Sub

Sub InvEmergs()

    Sheets(Array("Invoice", ActiveSheet.Name)).Copy
    Range("J22").FormulaR1C1 = "=SUM(Emergencies!C[17])"

End Sub

Sub InvADV()

    Sheets(Array("Invoice", ActiveSheet.Name)).Copy
    Range("J22").FormulaR1C1 = "=SUM(ADV!C[17])"

End Sub

Sub InvSSE()

    Sheets(Array("Invoice", ActiveSheet.Name)).Copy
    Range("J22").FormulaR1C1 = "=SUM(SSE!C[17])"

End Sub

Sub InvUKMA()

    Sheets(Array("Invoice", ActiveSheet.Name)).Copy
    Range("J22").FormulaR1C1 = "=SUM(UKMA!C[17])"

End Sub

Sub InvUKGC()

    Sheets(Array("Invoice", ActiveSheet.Name)).Copy
    Range("J22").FormulaR1C1 = "=SUM(UKGC!C[17])"

End Sub

How do I get the sheet name in each formula to change each time so that it refers to the sheet in the workbook that isn't "Invoice"?

Thanks in advance.

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