Sub LOC()
Dim n As Integer
Application.ScreenUpdating = False
BaoCao.Range("A5:A10000").ClearContents
BaoCao.Range("B6:H10000").ClearContents
Application.Goto Reference:="DL"
Selection.AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=Range("BaoCao!A5"), Unique:=True
BaoCao.Select
Range("A1").Select
BaoCao.Select
Range("A5:B5").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Sort Key1:=Range("A6"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
BaoCao.Select
n = Application.WorksheetFunction.CountA(Range("A6:A65000")) + 4
If n > 0 Then
Range("B6").Select
ActiveCell.Value = "=SUMIF(Data!$A$2:$G$65536,BaoCao!$A6,Data!$B$2:$B$65536)"
Selection.Copy
Range("B7:B" & n + 1).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("C6").Select
ActiveCell.Value = "=SUMIF(Data!$A$2:$G$65536,BaoCao!$A6,Data!$C$2:$C$65536)"
Selection.Copy
Range("C7:C" & n + 1).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("D6").Select
ActiveCell.Value = "=SUMIF(Data!$A$2:$G$65536,BaoCao!$A6,Data!$D$2:$D$65536)"
Selection.Copy
Range("D7:D" & n + 1).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("E6").Select
ActiveCell.Value = "=SUMIF(Data!$A$2:$G$65536,BaoCao!$A6,Data!$E$2:$E$65536)"
Selection.Copy
Range("E7:E" & n + 1).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("F6").Select
ActiveCell.Value = "=SUMIF(Data!$A$2:$G$65536,BaoCao!$A6,Data!$F$2:$F$65536)"
Selection.Copy
Range("F7:F" & n + 1).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("G6").Select
ActiveCell.Value = "=SUMIF(Data!$A$2:$G$65536,BaoCao!$A6,Data!$G$2:$G$65536)"
Selection.Copy
Range("G7:G" & n + 1).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("B6:G" & n + 1).Value = Range("B6:G" & n + 1).Value
End If
Application.ScreenUpdating = True
End Sub
