I am writing code to copy a range of cells in Workbook A, then open Workbook
B and paste them as a link. Before they are pasted in Workbook B, however,
my code searches for the first available empty row to paste the link.
Farther below where the paste link is to occur, I have an existing row of
Totals that sums all the previous stuff I've pasted.

The problem I encounter is that once the first empty row is found, I want to
insert 5 rows first (so that my Totals row gets pushed down to make room),
then paste the link. However, after the code I wrote to insert the five rows
executes, the clipboard loses the copied cells. Is there a way to paste a
link and insert the rows I want without losing the contents of the clipboard?
Thanks for all your suggestions!

Just change the order of things.

Find that row, insert the new rows, then copy the range and paste special links.

dim myRngToCopy as range
dim DestCell as range

set myrngtocopy = somerangeyouknowabout

'code to find the destination cell
set destcell = whateveryouneed here

application.goto destcell
myrngtocopy.copy
activesheet.paste link:=true




Steve C wrote:

I am writing code to copy a range of cells in Workbook A, then open Workbook
B and paste them as a link. Before they are pasted in Workbook B, however,
my code searches for the first available empty row to paste the link.
Farther below where the paste link is to occur, I have an existing row of
Totals that sums all the previous stuff I've pasted.

The problem I encounter is that once the first empty row is found, I want to
insert 5 rows first (so that my Totals row gets pushed down to make room),
then paste the link. However, after the code I wrote to insert the five rows
executes, the clipboard loses the copied cells. Is there a way to paste a
link and insert the rows I want without losing the contents of the clipboard?
Thanks for all your suggestions!

--

Dave Peterson