Code gộp các sheet trong file excel vào 1 sheet Tổng hợp

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 = Application.InputBox(“CHON GOP BAT DAU TU DONG NAO”, “Giangtranvn.com”, 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