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.

 

[Peter_SSs]

Like this?

1. Before copying the new data, select the entire sheet by clicking the box at the top left of the sheet
   
   
   
   
   
   
2. Select cell A1 in the sheet where you are going to paste
   
   
3. Paste

 

[tigerzen]

Like this?

1. Before copying the new data, select the entire sheet by clicking the box at the top left of the sheet
   
   View attachment 118589
   
   
2. Select cell A1 in the sheet where you are going to paste
   
   
3. Paste

Thanks Peter, if the entire sheet needs to be overwritten, then CTRL + A will work fine. I have some sheets where the some of the information needs to be retained, it may contain formulae etc. so I need to copy the data into say B15 onwards.

 

[Peter_SSs]

Then if you don't want to use vba to do the job I think that you will simply have to remember to clear all the old data first.

 

[tigerzen]

Then if you don't want to use vba to do the job I think that you will simply have to remember to clear all the old data first.

I was afraid of that. It might be the way to go as I have to do this several times a day. Maybe a simple macro that can easily be adapted across a number of files will do the trick, if there's no other way.

 

[Alex Blakenburg]

I assume column A contains formulas but what is in rows 1-14 ?

 

[tigerzen]

I assume column A contains formulas but what is in rows 1-14 ?

Slicers, headings, navigation aids

 

[Alex Blakenburg]

Have you considered using an actual Excel Table for the actual data range ?

 

[tigerzen]

Have you considered using an actual Excel Table for the actual data range ?

That might be ok, so that would mean you convert the data dump I receive into a table but how do you tell Excel where the table is going to start from?

 

[Alex Blakenburg]

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