range copy paste error

[Mr vdC]

Sub Macro3()
'
' Macro3 Macro
' copy
'

'
Range("J8:J397").Select
Selection.Copy
Range("J398:J227458").Select
ActiveSheet.Paste
Application.CutCopyMode = False
End Sub

when i try the above code, excel only pastes from J398:J787 where I was expecting it to repeat the pattern of formulas 540 odd times.

any help would be greatly appreciated.

kind regards

 

[Joe4]

Welcome to the Board!

Here is one way:

Sub Macros3()

    Dim r As Long
    Dim i As Long

    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

'   Designate first row to copy to
    r = 398
    
'   Loop 540 times
    For i = 1 To 540
'       Copy block
        Range("J8:J397").Copy Range("J" & r)
'       Move starting row 390 rows
        r = r + 390
    Next i

    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True

    MsgBox "Macro complete!"

End Sub

 

[Peter_SSs]

excel only pastes from J398:J787 where I was expecting it to repeat the pattern of formulas 540 odd times.

The reason that it does not repeat down is that it does not know where to stop. The reason that it does not know where to stop is that the size of the destination range is not an even multiple of the size of the copied range. If you ensure there is an even multiple then it will repeat, For example, this will copy to all the cells in J398:J227377

Range("J8:J397").Copy Destination:=Range("J398:J227377")

 

[Mr vdC]

Welcome to the Board!

Here is one way:

Sub Macros3()

    Dim r As Long
    Dim i As Long

    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

'   Designate first row to copy to
    r = 398
   
'   Loop 540 times
    For i = 1 To 540
'       Copy block
        Range("J8:J397").Copy Range("J" & r)
'       Move starting row 390 rows
        r = r + 390
    Next i

    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True

    MsgBox "Macro complete!"

End Sub

many thanks, worked like a charm

 

[Mr vdC]

The reason that it does not repeat down is that it does not know where to stop. The reason that it does not know where to stop is that the size of the destination range is not an even multiple of the size of the copied range. If you ensure there is an even multiple then it will repeat, For example, this will copy to all the cells in J398:J227377

Range("J8:J397").Copy Destination:=Range("J398:J227377")

i probably should have checked the maths first....thanks for you help

 

[Joe4]

many thanks, worked like a charm

You are welcome.

If your paste range is an exact multiple of your copy range, then you can use Peter's solution.
I would go with that, if possible, as then you do not need to use any loops!