copy range value x times in another sheet

Hi, nikenis,

you should make Cells inside your Range to relate to the destination sheet as well with adding a dot before them.

You would need to adjust your code to write cells to (maybe with End(xlUp).Row + 1):

Sub CopyRotaBase()

Dim wsPerp As Worksheet
Dim i As Long
Dim oCol As Long

Const clngRowsWide As Long = 5

Set wsPerp = Worksheets("RotaPerp")

With wsPerp
    For i = 2 To 2 'test on row 2 --> Rota 1A, Rota 1B will be on row 3...
        oCol = .Cells(i, Columns.Count).End(xlToLeft).Offset(, 1).Column 'locate the next empty cell
        .Cells(i, oCol).Resize(clngRowsWide, 6).Value = Sheets("RotaBase").Range("C" & i & ":I" & i).Value
    Next i
End With
End Sub

Ciao,
Holger

Hi, nikenis,

by using another loop:

Sub CopyRotaBase_3()

Dim wsPerp As Worksheet
Dim lngRow As Long
Dim lngCol As Long
Dim lngCounter As Long

Const clngREP As Long = 5

Set wsPerp = Worksheets("RotaPerp")

With wsPerp
    For lngRow = 2 To 2 'test on row 2 --> Rota 1A, Rota 1B will be on row 3...
        For lngCounter = 1 To clngREP
            lngCol = .Cells(lngRow, Columns.Count).End(xlToLeft).Offset(, 1).Column 'locate the next empty cell
            .Cells(lngRow, lngCol).Resize(1, 6).Value = Sheets("RotaBase").Range("C" & lngRow).Resize(1, 6).Value
        Next lngCounter
    Next lngRow
End With

Set wsPerp = Nothing

End Sub

Ciao,
Holger