Copy/Paste

[Guest]

The following code has worked good for me in the past:

Dim rng As Range
Set rng = ActiveSheet.AutoFilter.Range
rng.Copy Destination:=Worksheets("Old Data").Range("A1")
Sheets("Old Data").Range("A1").EntireRow.Delete Shift:=xlUp

However, I now want it to "Insert the Copied Data" at the destination and
shift the existing data down.

The code currently allows the data to be pasted over any existing data???

 

[Don Guillett]

try

with worksheets("Old Data")
Set rng = ActiveSheet.AutoFilter.Range
rng.Copy .Range("A" & .cells(rows.count,"a").end(xlup).row+1)
end with

 

[JE McGimpsey]

One way:

Dim rng As Range
Dim nRows As Long
Set rng = ActiveSheet.AutoFilter.Range
nRows = rng.Columns(1).SpecialCells(xlCellTypeVisible).Count
With Worksheets("Old Data")
.Range("A1").Resize(nRows).EntireRow.Insert Shift:=xlDown
rng.Copy Destination:=.Range("A1")
.Range("A1").EntireRow.Delete
End With

 

[JE McGimpsey]

That'll put the data (plus the headers) at the bottom of the range. If I
read correctly, the OP wanted a way to shift existing data down instead.