Bom dia!!

Faça os testes

Sub CopyAleVBA_8297()
    Worksheets("Plan1").Range("B15:C1000").Copy Worksheets("Plan2").Range("B" & Rows.Count).End(xlUp).Offset(15)
End Sub

Att

Alexandre, ficou perfeito valeu !!!!

obrigado,
frs

Bom dia!!

Obrigado pelo retorno!!!

Caso queir aprender mais sobre o assunto, aprenda com uma das maiores feras do excel (ron debruin_MVP)
http://www.rondebruin.nl/win/s3/win001.htm

Veja seu código daptado.

Sub AleVBA_Adaptado()
    Dim smallrng As Range, DestRange As Range
    Dim DestSheet As Worksheet, Lr As Long
    Dim SourceRange As Range, I As Integer

    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With

    'fill in the Source Sheet and range
    Set SourceRange = Sheets("Sheet1").Range("B15:C1000")

    'Fill in the destination sheet and call the LastRow
    'function to find the last row
    Set DestSheet = Sheets("Sheet2")
    Lr = LastRow(DestSheet)
    I = 2

    For Each smallrng In SourceRange.Areas

        'We make DestRange the same size as smallrng and use the
        'Value property to give DestRange the same values
        With smallrng
            Set DestRange = DestSheet.Cells(Lr + 1, I) _
                            .Resize(.Rows.Count, .Columns.Count)
        End With
        DestRange.Value = smallrng.Value
        I = I + smallrng.Columns.Count

    Next smallrng

    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With

End Sub
Function LastRow(sh As Worksheet)
    On Error Resume Next
    LastRow = sh.Cells.Find(What:="*", _
                            After:=sh.Range("A1"), _
                            Lookat:=xlPart, _
                            LookIn:=xlFormulas, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlPrevious, _
                            MatchCase:=False).Row
    On Error GoTo 0
End Function
Function LastCol(sh As Worksheet)
    On Error Resume Next
    LastCol = sh.Cells.Find(What:="*", _
                            After:=sh.Range("B1"), _
                            Lookat:=xlPart, _
                            LookIn:=xlFormulas, _
                            SearchOrder:=xlByColumns, _
                            SearchDirection:=xlPrevious, _
                            MatchCase:=False).Column
    On Error GoTo 0
End Function

Att