Macro - Copy and Paste repeatedly skipping cells

[pmsorensen]

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]

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.

 

[Max]

Perhaps one formulas play which could also deliver it in an adjacent
col ..

Assuming the required repetitive operation is to start in A7 down,

Place in B7:
=IF(OR(MOD(ROW(A1)-1,8)={6,7}),"",OFFSET(INDIRECT("A"&INT((ROW(A1)-1)/8)*8+7),,))
Copy B7 down as far as required

Col B should return the desired repetitive "copy-paste-skip" results
Then just copy col B to overwrite col A with a paste special as values

 

[Dave Peterson]

I thought that there might be existing data that should be touched (the reason
for skipping a couple of rows).

 

[Max]

Then perhaps just a slight tweak ..

In B7, copied down:
=IF(OR(MOD(ROW(A1)-1,8)={6,7}),A7,OFFSET(INDIRECT("A"&INT((ROW(A1)-1)/8)*8+7),,))

 

[pmsorensen]

Dave,

Thanks for the help. It worked perfectly.