Consolidate 7 worksheet into 1 worksheet if "Application no = Blank"

Dear All,

Seeking for professional help again.

I need to generate a non-application report every month.
What I currectly doing is copy & paste. It really time consuming.

I have attached the workbook for clear understanding.

There are total 7 worksheet which is segregate by department.

I'm required to provide non-application information of each department.
And would need to compile all non-application information together at one worksheet for the 7 departments.

Appreciate your help