Advanced filter / Excel VBA

Good day, I have to make an inspection report for emergency lighting but I got stuck with my macro.

Starting position:

- I have an Excel file with a number of worksheets: Front | Text | Basement | 1st Floor | 2nd Floor | Rejection points
- Front, Text and Rejection points are fixed, the worksheets in between are variable in number and content. On these intermediate worksheets I want to apply an advanced filter to column C ("Wrong"), copy and paste the result on the last worksheet “Rejection points”.


Situation:

- I am able to extract the data from ONE worksheet using copy and paste an advanced filter on the Rejection points worksheet.
- I am NOT able to make a loop of this so that all data ends up on the Rejection points worksheet.


Can someone help me? I have attached my file for your reference.