Copy and paste from multiple worksheets in to an excel table (keeps deleting data??)

[Weggs]

Hi all,

I'm having great difficulty and I'm hoping the Brains Trust can help me.

The task I'm trying to achieve, copy data from a 'new' spreadsheet (about 28 columns and 2.5k rows), then open up an 'existing' spreadsheet and paste the data, sounds simple right? Both workbooks have identical headers, so only the amount of rows may change. If the 'existing' workbook has more rows than the 'new' one, delete the 'existing' table of all rows (excluding headers), then paste the 'new' data in its place.

The problems I am hitting:

1. If I use the delete rows function from cell A2 (leaving the header row) to FirstFreeRow it deletes all the rows, including the header and the table is no longer a table.
2. If I use the Paste cells function from A2 to FirstFreeRow it works fine, but if existing worksheet had 50 more rows than the data that was pasted, then there are 50 additional rows of data (which will cause duplicates and corrupt the end results).
3. If I try and be dodgy and tell it to paste an additional 100 rows to blank out potential additional data, it doesn't do it.
4. If I use variables to define FirstFreeRow of the 'new' and the 'existing' and then tell it to delete cells starting from 'new' FirstFreeRow to 'existing' FirstFreeRow, for some reason it deletes all the rows, including the header and the table is no longer a table.

Essentially anything I try with Delete Cells is clearing all the data, including headers and removing the table.

I'm open to other ways to figure this out, so any suggestions you may have would be greatly appreciated.

[VJR]

@Weggs Please share the properties of the action that deletes the rows.

[Kaif_Siddique · Accepted solution]

Hi @Weggs,

Use Delete form Excel worksheet action:

To make it dynamic use Get first free column/row from Excel worksheet action.

Hope this will help you.

Regards

Kaif

[Weggs]

Hi VJR,

Here is what I am using. As you can see I'm telling PAD to delete the cells from A2 - firstfreecolumn and row.

[Weggs]

Ok, thats the wrong screen shot, thats a read one, I will post the proper screenshot in a moment

[Weggs]

@Kaif_Siddique  The one thing in your screenshot that I had different, made the difference, I had 'Shift Direction' set to left, when I changed it to 'up' that stopped it from removing all the headers and removing the table, it also removed the additional rows that was in the existing worksheet (the new data had 10 less lines than the existing data as a test to ensure all additional lines were being removed).

As far as I'm concerned, this is resolved. Thanks to you and @VJR I appreciate your help, thanks!