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?

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

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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
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?

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



Max wrote:

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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
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

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),,))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Dave Peterson" wrote in message
...
I thought that there might be existing data that should be touched (the
reason
for skipping a couple of rows).

Dave,

Thanks for the help. It worked perfectly.


Dave Peterson wrote:
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