Hello,
the best approach is not to duplicate the data. Instead of using VBA to copy the data, you can create a report that filters the data on the entry sheet to show only the rows you want to see.
Enter your data into one flat table, no blank columns, no blank rows.
Turn that data entry table into an Excel Table object (with Ctrl-T), then new rows will automatically inherit formulas, formats and drop-downs.
Click any cell in that table and Insert > Pivot Table on a new worksheet.
Drag the fields you want to see into the rows area, format the pivot table to use a tabular layout.
You can use filters or slicers to show only the data you want to see.
This is just a rough overview of the steps. There is a lot more detail to this, but there are lots of tutorials about pivot tables on the web.
