Repeating macro

[elmalle]

Hello everyone,

I am looking for a marcro that can copy and paste cells.

The value of cell X must be copied to a cell X + 6.
So A1 text "Xteam" has to be copied to cell A7, this up to cell A380.
The same applies to cell B2 + 6.

How can I do this,
I can not do it

thank you in advance
elmalle

 

[alansidman]

In your example if we loop through column A, when I get to cell A7 which now contains the A1 value, I will paste that value from A7 (which originally came from A1) into A13. Is this what you are looking to do? Are all cells in Column A from 1 to 380 currently populated?

As to column B. Starting in B2 and only going down 5 rows to paste and do this to B380? Am I understanding your needs correctly?

 

[JoeMo]

Assuming you start with values only in A1 and B2 this will fill down with the sequence you want.

Sub elmalle()
Const Nxt As Long = 7
Dim A As Variant, B As Variant, V As Variant, N As Long
A = Range("A1").Value
B = Range("B2").Value
ReDim V(Nxt To 380, 1 To 2)
For N = Nxt To 380
    If N Mod 6 = 1 Then
        V(N, 1) = A
        V(N + 1, 2) = B
    End If
Next N
Application.ScreenUpdating = False
Range("A" & Nxt, "B380").Value = V
Application.ScreenUpdating = True
End Sub

 

[elmalle]

Thanks you, is its also possible to have a max.. max of 60 times and then stop

 

[JoeMo]

I'll assume your thanks are directed to post #3 . Here's a modification that will allow you to set the max number of repeats of the A1:B2 content starting in row 7.

Sub elmalle2()
Const Nxt As Long = 7  '1st row to enter a repeat of A1:B2 contents
Const NumTimes As Long = 60  'number of times to repeat the A1:B2 contents
Dim A As Variant, B As Variant, V As Variant, N As Long
A = Range("A1").Value
B = Range("B2").Value
ReDim V(Nxt To (Nxt - 1) * NumTimes + 1 + Nxt, 1 To 2)
For N = Nxt To (Nxt - 1) * (NumTimes) + 1
    If N Mod 6 = 1 Then
        V(N, 1) = A
        V(N + 1, 2) = B
    End If
Next N
Application.ScreenUpdating = False
Range("A" & Nxt, Range("B" & (Nxt - 1) * NumTimes + 1 + Nxt)).Value = V
Application.ScreenUpdating = True
End Sub