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

[anuwers]

Please help me in this

i have the sample workbook, refer to the attached.
.
I need to get the report as attached in XL with sheet name “ReportRequired”.
.
Basically I have 4 sheets currently and I have named the branch name as 001 , 002 , 003, 004. Also will have more sheets as well.
.
Each sheet contains the similar column headers and
We write the serial number in column C
We write the other data’s is other fields. Specially, we write currency in column D
On each dates we continue the serial number of the concern branches.
.
What we required is as a report
Last serial number available each sheet.
Missing number. If the currency is missed in column D, then that serial suppose to be missed.
.
Please refer to the excel and help me in VBA codes to execute the reports.
.
Thanks in Advance

[LeoTaxi]

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 IsNumeric(sh.Name) Then
        my_sheet = Format(sh.Name, "000")
        
        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

[anuwers]

DEAR LEO,

Thanks for the code. My some of the sheet name carries alphabets as well. Code is not working if the sheet name carries alphabet.

Thanks

[LeoTaxi]

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

[anuwers]

Thanks Leo,
Its perfectly working.

Thanks a lot.