Code tìm các file dữ liệu cần gộp
Sub GopfileTH()
Dim FilesToOpen
Dim x As Integer
On Error GoTo ErrHandler
Application.ScreenUpdating = False
FilesToOpen = Application.GetOpenFilename _
(FileFilter:=”Microsoft Excel Files (*.xls*), *.xls*”, MultiSelect:=True, Title:=”Files to Merge”)
If TypeName(FilesToOpen) = “Boolean” Then
MsgBox “Khong co file nao duoc chon”
GoTo ExitHandler
End If
x = 1
While x <= UBound(FilesToOpen)
Workbooks.Open Filename:=FilesToOpen(x)
Sheets().Move After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
x = x + 1
Wend
ExitHandler:
Application.ScreenUpdating = True
Exit Sub
ErrHandler:
MsgBox Err.Description
Resume ExitHandler
End Sub
Code gộp file vừa chọn
Sub Gopfile()
Dim FilesToOpen
Dim Filetonghopmoi As Workbook
Dim filedangmo As Workbook
Dim strFilename As String
Dim x As Integer
On Error GoTo ErrHandler
Application.ScreenUpdating = False
FilesToOpen = Application.GetOpenFilename _
(FileFilter:=”Microsoft Excel Files (*.xls*), *.xls*”, MultiSelect:=True, Title:=”Files to Merge”)
If TypeName(FilesToOpen) = “Boolean” Then
MsgBox “Khong co file nao duoc chon”
GoTo ExitHandler
End If
‘=====
‘Tao file tong hop moi noi luu mac dinh ngoai desktop
Set Filetonghopmoi = Workbooks.Add(xlWBATWorksheet)
strFilename = Dir(“C:UsersAdministratorDesktop” & “*.xls”, vbNormal)
x = 1
While x <= UBound(FilesToOpen)
Set filedangmo = Workbooks.Open(Filename:=FilesToOpen(x))
Sheets(“PL”).Copy After:=Filetonghopmoi.Sheets(Filetonghopmoi.Sheets.Count)
‘Thay ten sheet can gop cho phu hop. o day dang gop tat ca cac sheet co ten “PL” o ca file da cho
Sheets(1).Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
filedangmo.Close False
x = x + 1
Wend
ExitHandler:
Application.ScreenUpdating = True
Exit Sub
ErrHandler:
MsgBox Err.Description
Resume ExitHandler
End Sub
———
Code gộp sheet trong file
Option Explicit
‘ Tao chuc nang tim ra DONG cuoi cung
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
‘ Tao chuc nang tim ra COT cuoi cung
Function Lastcol(sh As Worksheet)
On Error Resume Next
Lastcol = sh.Cells.Find(What:=”*”, After:=sh.Range(“A1”), Lookat:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, MatchCase:=False).Column
On Error GoTo 0
End Function
‘ Tao lenh GOPSHEET
Sub gopsheet()
Dim sh As Worksheet
Dim DestSh As Worksheet
Dim Last As Long
Dim shLast As Long
Dim CopyRng As Range
Dim StartRow As Long
With Application
.ScreenUpdating = False
.EnableEvents = False
End With
‘ XÓA bo sheet TONGHOP neu no da duoc thiet lap
Application.DisplayAlerts = False
On Error Resume Next
ActiveWorkbook.Worksheets(“TONGHOP”).Delete
On Error GoTo 0
Application.DisplayAlerts = True
‘ TAO 1 sheet moi ten TONGHOP
Set DestSh = ActiveWorkbook.Worksheets.Add
DestSh.Name = “TONGHOP”
‘Nhap dong bat dau
StartRow = 2
‘ Chay qua tat cac cac sheet va thuc hien copy du lieu vao sheet TONGHOP
For Each sh In ActiveWorkbook.Worksheets
If sh.Name <> DestSh.Name Then
‘ Tim don cuoi cung trong sheet tong hop va sheet thanh phan
Last = LastRow(DestSh)
shLast = LastRow(sh)
‘ Thuc hien copy du kieu khi sheet du lieu KHONG RONG va dong CUOI > dong BAT DAU
If shLast > 0 And shLast >= StartRow Then
‘ thiet lap VUNG can copy
Set CopyRng = sh.Range(sh.Rows(StartRow), sh.Rows(shLast))
‘ Kiem tra xem sheet TONGHOP co du dong de copy sang khong
If Last + CopyRng.Rows.Count > DestSh.Rows.Count Then
MsgBox “Khong du dong trong bang TONGHOP de copy sang”
GoTo ExitTheSub
End If
‘ Copy du lieu o dang GIA TRI
CopyRng.Copy
With DestSh.Cells(Last + 1, “A”)
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteFormats
Application.CutCopyMode = False
End With
End If
End If
Next
ExitTheSub:
Application.GoTo DestSh.Cells(1)
‘ Tu dong dieu chinh so cot theo sheet TONGHOP
DestSh.Columns.AutoFit
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub
————-
Code xóa hết các sheet vừa thêm
‘————————-
Sub XoaSheetVuaThem()
‘
Application.DisplayAlerts = False
Dim sh As Worksheet
For Each sh In Worksheets
If sh.Name <> ActiveSheet.Name Then
sh.Delete
End If
Next sh
Application.DisplayAlerts = True
End Sub
