How to paste/add data into table that already has data without overwriting it?

[Immy110]

Hello all,

so I have 1000 rows of data already in one table. I want to add another lot in addition to it on TOP. How do I do this without overwriting the current data?

Thanks

 

[Fishboy]

Hello all,

so I have 1000 rows of data already in one table. I want to add another lot in addition to it on TOP. How do I do this without overwriting the current data?

Thanks

Hi Immy110,

Are you referring to doing this manually or with a macro?

Manual way:

- Select and copy the desired data.
- Go to the top row of your table, right-click and select Insert. When prompted select you want to shift the existing data down.

VBA / Macro method:

- Press Alt+F11 to open the VBA window
- Find your workbook in the list on the lift, right-click and select Insert->Module
- In the new window that opens up, copy and paste the following code:

Sub InsertShiftDown()
' Copy from source sheet and range (modify sheet names and ranges as required)
Sheets("Sheet1").Range("A1:C1").Copy
' Insert copied data to top row of your table, shifting the existing data down (modify sheet names and ranges as required)
Sheets("Sheet2").Range("A1").Insert Shift:=xlDown
' Turns off the "marching ants" dotted line around the source cells
Application.CutCopyMode = False
End Sub

 

[Bob Redfield]

The manual method provided in a previous post inserts blank row(s) to which additional data can copied, but does not copy the data in the same process. Per Excel Help (under 'Insert or delete cells, row, and columns')..."You can insert cells that contain data and formulas by copying or cutting the cells, right-clicking the location where you want to paste them, and then clicking Insert Copied Cells or Insert Cut Cells." I have successfully used this method many times, however, recently the "Insert Copied Cells" no longer shows on the drop down menu after I right-click the row header location where I want to paste. I cannot find a relevant setting that may have gotten changed or any other new structure changes. And I'm not aware of any software upgrades. Any suggestions from anyone of what may have changed that could cause the "Insert copied rows" to disappear from the drop down menu would be appreciated.

 

[Fishboy]

Go to the top row of your table, right-click and select Insert

Well spotted! I had meant to say Insert copied cells as is evident by this part...

When prompted select you want to shift the existing data down

But clearly got carried away and didn't finish the sentence properly.

 

[Fishboy]

I have successfully used this method many times, however, recently the "Insert Copied Cells" no longer shows on the drop down menu after I right-click the row header location where I want to paste. I cannot find a relevant setting that may have gotten changed or any other new structure changes. And I'm not aware of any software upgrades. Any suggestions from anyone of what may have changed that could cause the "Insert copied rows" to disappear from the drop down menu would be appreciated.

Hmm, not too sure what would be the definitive cause of that, what version of Excel are you using?