Macro to delete rows that have a date outside 20 business day window

First, I recommend you paste the data into an Excel Table. Excel tables have many benefits; among them are: they know how big they are so any formulas, pivot tables or charts built from them adjust automatically as you add or delete rows, they copy down automatically, formulas, formats and data validations.

The way I would attack this is to delete the old data in the table and copy in the new data. Have a cell with the formula =Today()-20 and give it a name like Day20.

In the table have a helper cell at the end with the formula [@[Date]] >= Day20. (or use cells Z1 and Z2 on the other sheet). This should evaluate to TRUE for any date within 20 days of the current day. When you delete the data in the table so you can paste in new data, the table will "remember" this formula and copy it down for each row in the new table.

Then build a pivot table using this helper column as a filter set to TRUE.

Some additional automation. Read the daily data automatically, clear the report table and copy paste the daily data. Take the results of the pivot table sheet, save it as a PDF and attach it to the emails. Of course also copy paste the new data to the archive.

If you can provide two sample workbooks (data file) and (report file), we can get you set up.

Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

OK. In this case do exactly the same thing, then write a macro that goes from the last row to the first row step -1 looking at the helper column. When it finds FALSE (outside 20 days), delete the entire row.