Hi all,

Here is below a code that copy the desired source to destination. I would like to add a constant that will be used to copy the source x number of times.

Sub CopyRotaBase()

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

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
                    .Range(Cells(i, oCol), Cells(i, oCol + 6)).Value = Sheets("RotaBase").Range("C" & i & ":I" & i).Value
          Next i
    End With
End Sub