Snasui Excel Forum

Code: Select all

Sub GenReport02()
    Dim rCus As Range, rDtl As Range
    Dim rc1 As Range, rc2 As Range, rc3 As Range
    Dim rc As Range, rt As Range, rSum As Range
    Dim Lng As Long, iCount As Integer
    With Sheets("Table Customer")
        Set rCode = .Range("A2", .Range("A" & Rows.Count).End(xlUp))
    End With
    For Each rc In rCode
        With Sheets("Table Detail")
            iCount = Application.CountIf(.Range("A2", .Range("A" & Rows.Count).End(xlUp)), rc)
            Lng = Application.Match(rc, .Range("A2", .Range("A" & Rows.Count).End(xlUp)), 0) + 1
            If iCount > 0 Then
                Set rDtl = .Range("B" & Lng, .Range("E" & Lng + iCount - 1))
            End If
        End With
        Set rc1 = rc.Offset(0, 2).Resize(1, 4)
        Set rc2 = rc.Offset(0, 6).Resize(1, 3)
        Set rc3 = rc.Resize(1, 2)
        Set rt = Sheets("Trial").Range("M" & Rows.Count).End(xlUp).Offset(1, -12)
        rc1.Copy: rt.PasteSpecial xlPasteValues
        rc3.Copy: rt.Offset(0, 4).PasteSpecial xlPasteValues
        rc2.Copy: rt.Offset(0, 6).PasteSpecial xlPasteValues
        rDtl.Copy: rt.Offset(0, 9).PasteSpecial xlPasteValues
        Set rSum = rt.Offset(0, 12).Resize(iCount, 1)
        rt.Offset(0, 12).End(xlDown).Offset(1, 0) = Application.Sum(rSum)
    Next rc
End Sub

Code: Select all

Set rSource = .Range("A2", .Range("M" & Rows.Count).End(xlUp))

Code: Select all

Option Explicit
Sub GenReport()
    Dim rSource As Range
        With Sheets("Expect")
        Set rSource = .Range("A2", .Range("M" & Rows.Count).End(xlUp))
    End With
        Sheets("Trial").Range("A2:M100").Clear
        rSource.Copy
        Sheets("Trial").Range("A2").Select
        ActiveSheet.Paste
        Application.CutCopyMode = False
End Sub