How would I limit the data on my sheet?

Hey, first post.

My workbook has two sheets, an active sheet (sheet1) that is fully updated regularly from a website and a second sheet (sheet2) on which data from the first sheet is appended after first being sifted for duplicate rows and proper sequencing. The data is then saved.

This is fully automated via an hourly macro loop and seems to work well.

However, my problem is that overtime the data on sheet2 will grow in volume.

I would like to keep sheet2's data at around 1000 rows so as to minimize the memory footprint of that workbook but also regularly save that data without erasing the previously saved data beyond 1000 rows.

In essence, while sheet1 is active, I would like sheet2 to be less active (housing only 1000 rows), saving in an appending fashion all data from sheet2 to an archive from which my excel workbook no longer draws data.

Or perhaps said another way, I would like to dump data captured by excel into a file that is not opened or not fully drawn from when I open my excel workbook.

Should I have a separate workbook that copies data from the workbook above but does not erase the most recent 1000 entries?

Thanks upfront.