Automatically resize a table based on the source data

Solution 1:

There is a very simple answer to your troubles:

Reduce the table to the headers and one blank row. Just the one blank row. Either you have a single row, in which case, it simply fills it, or it will resize when you paste to precisely that many rows as are pasted. In no case does that situation of having fewer rows than the number already present in the table arise.

You CANNOT, however, use FILTER() (specifically, as it was mentioned in another answer) or ANY Spill function. They DO NOT WORK in Tables, not at all or in any circumstance. You can certainly place a set of formulas to obtain a row's data from the source, then copy and paste it down as needed, but that's got the same difficulty as the practice in your post.

Edit:

Just noticed a note about existing formulas. They will still exist in the single row, and will populate down when you paste. So unlike Ctrl-A, Delete, you do not lose them with the above approach.

If you do not need the data in the table saved at any point, this handles everything as the single line is restored after every use by not saving.

Of course, if you DO need to save, or just want to save, sometime prior to your next use with new data, you will have to... manually... clear the data down to one row... So the problem would just be transferred in time.

You can start the macro recorder, reduce the Table to the single row, clear the data in the row (leaving formulas alone), and end the recording. If you assign that a shortcut or make a button and link the macro to it, Excel will do the work for you each time.

If you do not save after each use, it will come up with the single row each time, so no problems. If you do, then you cannot use VBA OnOpen or OnSave macro initiation since it would wipe out your work, so that just leaves the shortcut keypress or a button for easy use. (Or hunting up the macro with the mouse and initiating it, though that's harder. Not a lot! But you ARE looking for as little interaction here as possible is my feel.)

Solution 2:

The simplest possible answer:

Wen you're about to paste; hold CTRL hit A, then Delete (the key) to remove the old data,
then paste the new (CTRL+V).

This of course assumes that you have the data alone on a sheet, no formulas intermixed.