Add formula only if sheet exists?

[TheWennerWoman]

I have a master sheet with lots of data and have a routine that splits it into a number of sheets, depending on the number of records chosen by the user. So if there are 100,000 records in the master sheet and the user choses 20,000, the routine will create five new sheets.

I am trying to future-proof this so have the following

Sheets("Control").Select
Range("E81").FormulaR1C1 = "=IFERROR(journal_1!R[-76]C[4],"""")"
Range("E82").FormulaR1C1 = "=IFERROR(journal_2!R[-77]C[4],"""")"
Range("E83").FormulaR1C1 = "=IFERROR(journal_3!R[-78]C[4],"""")"
Range("E84").FormulaR1C1 = "=IFERROR(journal_4!R[-79]C[4],"""")"
Range("E85").FormulaR1C1 = "=IFERROR(journal_5!R[-80]C[4],"""")"
Range("E86").FormulaR1C1 = "=IFERROR(journal_6!R[-81]C[4],"""")"
Range("E87").FormulaR1C1 = "=IFERROR(journal_7!R[-82]C[4],"""")"
Range("E88").FormulaR1C1 = "=IFERROR(journal_8!R[-83]C[4],"""")"
Range("E89").FormulaR1C1 = "=IFERROR(journal_9!R[-84]C[4],"""")"
Range("E90").FormulaR1C1 = "=IFERROR(journal_10!R[-85]C[4],"""")"
Range("E91").FormulaR1C1 = "=IFERROR(journal_11!R[-86]C[4],"""")"
Range("E92").FormulaR1C1 = "=IFERROR(journal_12!R[-87]C[4],"""")"

How can I amend the above to only add the formula if the sheet exists? In the above, journal_6 onwards wouldn't exist in this example.

 

[Peter_SSs]

Could you use something like this?

Sub Test()
  Dim i As Long
  Dim shName As String
  
  With Sheets("Control")
    For i = 1 To 12
      shName = "journal_" & i
      If ShtExists(shName) Then .Range("E" & 80 + i).FormulaR1C1 = "=IFERROR(" & shName & "!R5C[4],"""")"
    Next i
  End With
End Sub

Function ShtExists(SheetName As String, Optional wb As Workbook) As Boolean
  On Error Resume Next
  ShtExists = (IIf(wb Is Nothing, ActiveWorkbook, wb).Sheets(SheetName).Name <> "")
  On Error GoTo 0
End Function

 

[TheWennerWoman]

That is truly outstanding, many thanks!

 

[Peter_SSs]

You are welcome. Thanks for the follow-up.