Overwrite all data with minimum of steps

[tigerzen]

I have some files that need constant updating. Basically, I'm sent a file which I then copy and paste over another file. Is there a way to ensure that the new file information will not contain any of the old information. I've found that if the new file contains less rows then unless I'm careful, the old file records will appear at the end of the new file's information. I have to be disciplined in deleting all the old data first but when in a hurry I have forgotten to. Looking for a non-VBA/PQ way to do this if possible.

 

[tigerzen]

It partly depends on how flexible you are on the layout.
I typically had all my copied-in data on the left hand side of the table and all my formulas to the right. (with the columns colour coded to make the formula columns obvious)
So I would always be copying in the data to the top left corner of the table, which makes it easy to code in VBA.

By using a table the code, could look for the first table on the sheet and paste in the data from the clipboard.
A table would also automatically expand the number of rows in the table if you copied in more than the previous time and the formulas would auto populate the additional rows.

If you wanted to try to convert one of your sheets to a table and manually select where to copy it to try the code below:
Note: It doesn't have any error handling or checks so run it on a copy of your workbook.

Copy your new data into the clipboard.
Navigate to the cell you want to paste the data to, which I am assuming is in a table and also assuming it will be the first data row in the table (ie you are not appending but replacing)

Sub Paste()

    ' Go to source data, Select Data to Copy then Ctrl+C
    ' Go to destination table and select cell into which to copy the data and run macro

    Dim tbl As ListObject
    Dim tblRowCnt As Long
    Dim copyRowCnt As Long
   
    Set tbl = ActiveCell.ListObject
    tblRowCnt = tbl.DataBodyRange.Rows.Count
   
    ActiveCell.PasteSpecial Paste:=xlValues
    copyRowCnt = Selection.Rows.Count
   
    If tblRowCnt > copyRowCnt Then
        tbl.ListRows(copyRowCnt + 1).Range.Resize(tblRowCnt - copyRowCnt).EntireRow.Delete
    End If

End Sub

Thanks Alex, will give this a try, it won't be long before I start getting some data.