I have created a procedure to add 30-40 rows (variable number) of data to an existing excel Table. The procedure needs to transfer the data from one sheet (Import) to another sheet (Database). The Database sheet is a formatted excel table and the new data needs to be added to the bottom (first available blank row). I would like help making this copy process consistent and error free. [Note that the date that is copied is formula driven so it needs to be paste special values or directly referenced.
If doing a simple copy and paste the data is transferred but there are warnings about shifting cells below the table down. When i turned off alerts i had more success but occasional code errors such as incorrect referencing of the table list object. I found alternative methods where the table was resized using VBA but this also gave occasional errors/behaviour is not consistent. Any suggestions will be welcomed.