VBA help needed for copy & pasteing a range every 14 rows

[bigaz_123]

good afternoon everyone.

can you please help me, I have a data set & I need to copy A1:Z14 & paste as value stating at A35, I need to repeat this until the last populated row - 7 rows.

can anyone help me?

 

[JackDanIce]

Welcome to the board.

When macro runs:
First time copy A1:Z14 to A35 (i.e. A35:Z48)
Second time copy A1:Z14 to A40?
Continue until you reach the last populated row - 7 (e.g. if last row is 100, stop if the last pasted value is in row 93 or higher?)

Perhaps you can explain further, difficult to understand without seeing your sheet.

What column contains the last populated row?

 

[bigaz_123]

hi Jack.

Its a strange data set, is there a way of attaching the sheet so I can show you what needs to happen? ps the ranges I gave were for ref only they are actually JZ1:NQ14, I would like to paste the first one in to JZ35 then JZ49 and so on, currently the last cell to paste in to is JZ3493 but this reange need to be dynamic as this expands with new records.

regards,

 

[JackDanIce]

If you go to the Forum rules on this board, you'll find links to posting code, screenshots etc.

That's fine giving ranges for reference, but what column determines what the last row is, JI or A or other?

As a guess, test on a copy of your worksheet/workbook:

Sub RePopulate()

    Dim LR      As Long
    Dim x       As Long
    Dim arr()   As Variant
    
    'Write area JZ1:NQ14 into an array, where column JZ= 286
    arr = Cells(1, 286).Resize(14, 96).Value
    
    'Determine last row based on column JY
    LR = Cells(Rows.count, 285).End(xlUp).row
    'Reduce last row by 7
    LR = LR - 7
    
    Application.ScreenUpdating = False
        
    'Loop over range and paste values every 14 rows from array
    For x = 35 To LR Step 14 
        Cells(x, 286).Resize(UBound(arr, 1), UBound(arr, 2)).Value = arr
    Next x
    
    Application.ScreenUpdating = True
    
    Erase arr
    
End Sub

 

[bigaz_123]

Hi Jack.

this has worked a treat, thank you so much for your help.

regards.

 

[Fluff]

Another option

Sub CopyPaste()

    Dim UsdRws As Long
    
    UsdRws = Cells.Find("*", After:=Range("A1"), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    UsdRws = UsdRws / 14
    Range("M1:V14").Copy Range("M35").Resize(14 * UsdRws)

End Sub

Change ranges to suit

 

[JackDanIce]

You're welcome

Nice alt suggestion and shorter, thanks @Fluff

 

[Fluff]

@JackDanIce
Your welcome
Cheers