Macro to copy data from every nth row to another sheet

[sshirazi]

Need a macro to populate data from source "sheet1" to destination "Sheet2". The data to be copied from sheet1 is on every 43 row except the first & in 14 columns. e.g

From Row N41:O41 & R41:AC41 "Sheet1"
to Row N6:O6 & Q6:AB6 "Sheet2"
From Row N84:O84 & R84:AC84 "Sheet1"
To Row N7:O7 & Q7:AB7 "Sheet2"
From Row N127:O127 & R127:AC127 "Sheet1"
To Row N8:O8 & Q8:AB8 "Sheet2"

Thanks for help!

 

[Trebor76]

Hi sshirazi,

Here's one way:

Option Explicit
Sub Macro1()

    Dim varCellsFrom As Variant
    Dim varCellsTo As Variant
    Dim lngArrayIndex As Long
    
    Application.ScreenUpdating = False
    
    varCellsFrom = Array("N41:O41", "R41:AC41", "N84:O84", "R84:AC84", "N127:O127", "R127:AC127")
    varCellsTo = Array("N6:O6", "Q6:AB6", "N7:O7", "Q7:AB7", "N8:O8", "Q8:AB8")
    
    For lngArrayIndex = LBound(varCellsFrom) To UBound(varCellsTo)
        Sheets("Sheet1").Range(CStr(varCellsFrom(lngArrayIndex))).Copy Destination:=Sheets("Sheet2").Range(CStr(varCellsTo(lngArrayIndex)))
    Next lngArrayIndex
    
    Application.ScreenUpdating = True

End Sub

Regards,

Robert

 

[sshirazi]

Thanks a lot. That was great!

 

[Trebor76]

You're welcome. I'm glad we got it sorted