to find last number and missing number in series from various sheets

This takes all sheets except the ReportRequired

Sub test()
Dim sh As Worksheet, x As Long, my_sheet As String, my_string As String, mystring2 As String, last_entry As String
For Each sh In Sheets
    If sh.Name <> "ReportRequired" Then
        If IsNumeric(sh.Name) Then
            my_sheet = Format(sh.Name, "000")
            Else
            my_sheet = sh.Name
        End If
        last_entry = Sheets(sh.Name).Range("C" & Rows.Count).End(xlUp).Value
        lr = Sheets(sh.Name).Range("C" & Rows.Count).End(xlUp).Row
            For x = 2 To lr
                If Sheets(sh.Name).Range("D" & x) = vbNullString Then
                    mystring = mystring & "| " & Sheets(sh.Name).Range("C" & x).Value
                End If
            Next
            If Len(mystring) = 0 Then mystring2 = ""
            If Len(mystring) > 0 Then mystring2 = Right(mystring, Len(mystring) - 2)
        Sheets("ReportRequired").Range("A" & Rows.Count).End(xlUp).Offset(1).Resize(, 3) = Array(my_sheet, last_entry, mystring2)
        mystring = ""
    End If
Next
End Sub

Kind regards
Leo