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

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