Snasui Excel Forum

Code: Select all

Sub Test00()
    Dim rSource As Range
    Dim rTarget As Range
    With Sheets("Entry")
        Set rSource = .Range("a10", .Range("a163").End(xlUp))
    End With
    With Sheets("Report")
        Set rTarget = .Range("b" & .Rows.Count).End(xlUp).Offset(1, 0)
    End With
    rSource.Copy
    rTarget.PasteSpecial xlPasteValues
    rSource.Offset(0, 5).Copy
    rTarget.Offset(0, 1).PasteSpecial xlPasteValues
    rSource.Offset(0, 2).Copy
    rTarget.Offset(0, 2).PasteSpecial xlPasteValues
    Application.CutCopyMode = False
End Sub

Code: Select all

 Dim rSource As Range
    Dim rTarget As Range
    Dim LRs As Long 'last row
    Dim FR As Long 'first row
    With Sheets("Entry")
        'Set rSource = .Range("a10", .Range("a163").End(xlUp))
        LRs = .Cells.Find("Total", , , , xlByRows, xlPrevious).Row - 1
        FR = .Cells.Find(shTOM, , , , xlByRows, xlPrevious).Row + 1
        Set rSource = .Cells(LRs, FR).Offset(0)

        
    End With
    With Sheets("Report")
        Set rTarget = .Range("b" & .Rows.Count).End(xlUp).Offset(1, 0)
    End With
    rSource.Copy
    rTarget.PasteSpecial xlPasteValues
    rSource.Offset(0, 5).Copy
    rTarget.Offset(0, 1).PasteSpecial xlPasteValues
    rSource.Offset(0, 2).Copy
    rTarget.Offset(0, 2).PasteSpecial xlPasteValues
    
    Application.CutCopyMode = False