I am trying create a macro at the push of a button to copy data from a
certain cell or range of cells and then paste into another cell, however if
that cell I am pasting into has data in it I want it to go down 3 cells and
paste it, and so on and so on. I would imagine using the loop function would
work for me but I can't seem to figure it out.

Dim RngToCopy as range
Dim DestCell as range

with worksheets("Sheet999")
set rngtocopy = .range("a1") 'whatever you want
end with

with worksheets("sheet888")
set destcell = .range("x99") 'some starting cell
end with

do
if isempty(destcell.value) then
exit do
else
set destcell = destcell.offset(3,0)
end if
loop

rngtocopy.copy _
destination:=destcell


Jase wrote:

I am trying create a macro at the push of a button to copy data from a
certain cell or range of cells and then paste into another cell, however if
that cell I am pasting into has data in it I want it to go down 3 cells and
paste it, and so on and so on. I would imagine using the loop function would
work for me but I can't seem to figure it out.

--

Dave Peterson

Dave, thank you for the help but the data is just pasting over the DestCell
its not looking for the next available free cell.

"Dave Peterson" wrote:

Dim RngToCopy as range
Dim DestCell as range

with worksheets("Sheet999")
set rngtocopy = .range("a1") 'whatever you want
end with

with worksheets("sheet888")
set destcell = .range("x99") 'some starting cell
end with

do
if isempty(destcell.value) then
exit do
else
set destcell = destcell.offset(3,0)
end if
loop

rngtocopy.copy _
destination:=destcell


Jase wrote:

I am trying create a macro at the push of a button to copy data from a
certain cell or range of cells and then paste into another cell, however if
that cell I am pasting into has data in it I want it to go down 3 cells and
paste it, and so on and so on. I would imagine using the loop function would
work for me but I can't seem to figure it out.

--

Dave Peterson

Never mind I got it to work. Thanks Dave.


"Jase" wrote:

I am trying create a macro at the push of a button to copy data from a
certain cell or range of cells and then paste into another cell, however if
that cell I am pasting into has data in it I want it to go down 3 cells and
paste it, and so on and so on. I would imagine using the loop function would
work for me but I can't seem to figure it out.