find lastrow and chart last 200 enteries

hello
I have a wb that contains a shet of all my data and then when a certain sheet is selected i have a comand that:
Copys All data
Pastes all data in selected sheet
Filters specific data for that selected sheet.

I need to be able to chart the last 200 enteries on a third sheet.
I have tried multiple formulas but it appears that the program is still recognizing that there is data in the empty cells that have been filtered out.
Im not sure if i should use a formula or a comand.

Please let me know if you can help
I can provide a sample wb if needed
Thankyou!

Attached is sampl wb.

Sample workbook.xlsm

Thank you so much skywriter.

I know thats what will happen.
When i created it i imagined that the user will only go to the directory page if you want to change locations (cell selection), if not you can just click the sheet names at the bottom.

The only reason i have the hyperlink directory is so the program knows which sheets the user is trying to look at, and hide the rest.

But thanks again this works perfectly!

Is there anyway i can add a line that tells it to not show any blank rows?
when the data is pasted onto sheet 3 there is over a million blank rows that get loaded, which is creating some lagtime.

Thank you