Sub CopyData()
Dim hangcuoi As Integer
With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False
.DisplayAlerts = False
End With
Sheets("Sheet2").Select
Range("a1:B1000").ClearContents
'mo test1
'neu co the them kiem tra file co ton tai
Workbooks.Open Filename:="E:\TEST1.xls"
Windows("TEST1.xls").Activate
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.copy
'mo test total va dan
Windows("Test total.xls").Activate
Sheets("Sheet2").Select
Range("A1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
'dong test1
Windows("TEST1.xls").Activate
ActiveWindow.Close
'mo test2
Workbooks.Open Filename:="E:\TEST2.xls"
Windows("TEST2.xls").Activate
Range("A2").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.copy
Windows("Test total.xls").Activate
Sheets("Sheet2").Select
Range("A1").Select
'chon dong cuoi de dan
hangcuoi = Range(Selection, Selection.End(xlDown)).Rows.Count
Range("a" & hangcuoi + 1).Select
ActiveSheet.Paste
Application.CutCopyMode = False
'dong test2
Windows("TEST2.xls").Activate
ActiveWindow.Close
Sheets("Sheet2").Select
Range("A1").Select
hangcuoi = Range(Selection, Selection.End(xlDown)).Rows.Count
Sheets("Sheet2").Range("A1" & " :A" & hangcuoi).Name = "MAHANG"
Sheets("Sheet2").Range("B1" & " :B" & hangcuoi).Name = "SOLUONG"
'tao danh muc
Sheets("Sheet1").Select
'Cells(1, 1).Delete
Range("MAHANG").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _
"a1"), Unique:=True
'Lay soluong
Dim dongcuoi As Integer
Sheets("Sheet1").Select
Cells(1, 1).Select
dongcuoi = Range(Selection, Selection.End(xlDown)).Rows.Count
For i = 2 To dongcuoi
Cells(i, 2).Value = "=SUMIF(MAHANG,RC1,SOLUONG)"
Next i
'copy va dan gia tri
Range("A2:B" & dongcuoi).Select
Selection.copy
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Sheets("Sheet2").Select
Range("a1:B1000").ClearContents
Sheets("Sheet1").Select
Range("B1").Value = "SOLUONG"
Range("B1").Select
With Application
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
.DisplayAlerts = True
.Names("Extract").Delete
End With
End Sub