VBA Macro with button to update daily report with data from the previous day

Hello,
I’m hoping one of you can help me out here.

I get daily report (file name format is MMDDYY – All_PDRs_By_Location.xls)
Each day my team looks at this report and works items in their area. If an item does not belong to our team they enter “NASO”, “CPACT”, or some other text in column N.

The next day I get a new report (file name format is again MMDDYY - All_PDRs_By_Location.xls).
I need a macro with a button that will go out and get the report from the prior day and do a VLOOKUP on Column A to put the team name from Column N into the corresponding field on the new daily report.

It would be great if it also deleted lines 1 – 7 of the new report each day and renamed Column N to “CPACT or NASO?”

It would then save the new updated report as a file named “MMDDYY - All_PDRs_By_Location - UPDATED.xls” in a subfolder called PDR.

I have attached two days of reports to this post……any assistance would be greatly appreciated.