before....

1. you changed the values of I4 and I5 which is what you originally copied to the A2's for the filter criteria.
at 10:55 of your video A2 shows "Yes", but it shouldn't because you just change the cell it came from to "Registered".
But if you look at N17:N18 that's where the macro wrote to on the second pass. You didn't specify where to write to so it used the active cell.

2. you didn't remove the old data from the 'two' sheets before over writing it with the new filtered results


now...

Re: 1.) Removed any code that would populate A1 and A2 of the 'two' sheets.
The A1 never changes and the A2 you know what it should be, so just enter it once manually and you'll never need to touch it again.

Re: 2.) First thing that happens is everything beyond row 3 of the 'two' sheets is deleted then the advanced filter is applied.

In a nutshell, the Worksheet_Activate event of both the "Unregistered_Tenants" and "Registered_Tenants" sheets run 2 lines of code and the recorded macro can be removed.


ps: You do realize that hidden row 2 prevents manual filtering of the data, right ?