Sub MergeSheetsWithSTTOptimized()
Dim ws1 As Worksheet, ws2 As Worksheet, wsTONGHOP As Worksheet
Dim lastRow1 As Long, lastRow2 As Long, lastRowTONGHOP As Long
Dim data1 As Variant, data2 As Variant, mergedData As Variant
Dim i As Long, j As Long
Dim totalRows As Long
' Gán các sheet
Set ws1 = ThisWorkbook.Sheets("1")
Set ws2 = ThisWorkbook.Sheets("2")
Set wsTONGHOP = ThisWorkbook.Sheets("TONG HOP")
' Ki?m tra s? t?n t?i c?a các sheet
If ws1 Is Nothing Or ws2 Is Nothing Or wsTONGHOP Is Nothing Then
MsgBox "M?t trong các sheet '1', '2', ho?c 'TONG HOP' không t?n t?i.", vbExclamation
Exit Sub
End If
' T?t tính nang t? d?ng tính toán và c?p nh?t màn hình d? tang t?c d? x? lý
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
' Xóa d? li?u cu trên sheet "TONG HOP"
wsTONGHOP.Rows("2:" & wsTONGHOP.Rows.Count).ClearContents
' Copy tiêu d? t? sheet "1" sang sheet "TONG HOP"
ws1.Rows(1).Copy Destination:=wsTONGHOP.Rows(1)
' Xác d?nh dòng cu?i c?a m?i sheet
lastRow1 = ws1.Cells(ws1.Rows.Count, "A").End(xlUp).Row
lastRow2 = ws2.Cells(ws2.Rows.Count, "A").End(xlUp).Row
' Ð?c d? li?u t? các sheet vào m?ng
If lastRow1 > 1 Then
data1 = ws1.Range("A2:N" & lastRow1).Value
End If
If lastRow2 > 1 Then
data2 = ws2.Range("A2:N" & lastRow2).Value
End If
' Tính toán s? hàng c?n g?p
totalRows = IIf(lastRow1 > 1, UBound(data1, 1), 0) + IIf(lastRow2 > 1, UBound(data2, 1), 0)
' Ch? th?c hi?n n?u có d? li?u d? g?p
If totalRows > 0 Then
ReDim mergedData(1 To totalRows, 1 To 14)
' Sao chép d? li?u t? data1 vào mergedData
For i = 1 To UBound(data1, 1)
For j = 1 To 14
mergedData(i, j) = data1(i, j)
Next j
Next i
' Sao chép d? li?u t? data2 vào mergedData
For i = 1 To UBound(data2, 1)
For j = 1 To 14
mergedData(UBound(data1, 1) + i, j) = data2(i, j)
Next j
Next i
' Ghi d? li?u t? m?ng mergedData vào sheet "TONG HOP" m?t l?n
wsTONGHOP.Range("A2:N" & totalRows + 1).Value = mergedData
End If
' Ðánh s? th? t? (STT) ? c?t A c?a sheet "TONG HOP"
lastRowTONGHOP = wsTONGHOP.Cells(wsTONGHOP.Rows.Count, "B").End(xlUp).Row
If lastRowTONGHOP > 1 Then
wsTONGHOP.Range("A2:A" & lastRowTONGHOP).Formula = "=ROW()-1"
End If
' B?t l?i tính nang tính toán t? d?ng và c?p nh?t màn hình
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
' Thông báo hoàn t?t
MsgBox "Da xong !", vbInformation
End Sub
