I'm not sure I got the numbers right--you may want to test it against a small
subset of your data:

Option Explicit
Sub CopyPaste2()
Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long
Dim myStep As Long
Dim HowManyToPaste As Long

With ActiveSheet
FirstRow = 7
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
HowManyToPaste = 5
myStep = 8

For iRow = FirstRow To LastRow Step myStep
.Cells(iRow, "A").Copy _
Destination:=.Cells(iRow, "A") _
.Offset(1, 0).Resize(HowManyToPaste, 1)
Next iRow
End With

End Sub

I also used the data in column A to find the last cell to process.


wrote:

Hi all,

I am trying to write a macro that will start at a specified cell, copy
that cell and paste it into the next 5 cells below it. It then needs to
move down 2 cells from the last pasted cell (or 8 cells from the first
cell) and strat the process all over again. This will need to repeat
for 3000 to 4000 rows (the number will vary by week). I can get this to
work on the first group of cells. I am having trouble with the step
where it skips down to the next set of data and repeats again.

This is what I have so far:

Sub CopyPaste()
'
' CopyPaste Macro
' Macro recorded 1/4/2007 by pausor
'

'
Range("A7").Select
Selection.Copy
Range("A8:A12").Select
ActiveSheet.Paste
Range("A12").Select
End Sub

Admittedly not very fancy and the absolute referencing is also killing
me on this one.

Can someone point me in the right direction?

--

Dave Peterson