1. Dim filePathSource As String
2. Dim filePathTarget As String
3. Dim namePathSource As String
4. Dim checkedValues As String
5. Dim columnCheckFromSource As String
6. Dim titleSheetSourceString As String
7. Dim titleSheetTargetString As String
8.  
9. Private Sub ComboBox1_Change()
10.     Dim wb As Workbook
11.     Dim ws As Worksheet
12.     Dim lastCol As Integer
13.     Dim i As Integer
14.     Dim selectedColumn As String
15.    
16.     Dim selectedSheet As String
17.     selectedSheet = ComboBox1.Value
18.     titleSheetSource.Caption = selectedSheet
19.     titleSheetSourceString = titleSheetSource.Caption
20.  
21.     Set wb = Workbooks.Open(filePathSource)
22.    
23.  
24.     For Each ws In wb.Sheets
25.         If ws.Name = selectedSheet Then
26.          
27.             lastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
28.          
29.             ComboBox2.Clear
30.            
31.             For i = 1 To lastCol
32.                 ComboBox2.AddItem ws.Cells(1, i).Value
33.             Next i
34.              
35.             Exit For
36.         End If
37.     Next ws
38.    
39.    
40.    
41.    
42. End Sub
43.  
44. Private Sub ComboBox2_Change()
45.     Dim selectedData As String
46.     columnCheckFromSource = ComboBox2.Value
47.     sourceOriginalColumn = ComboBox2.Value
48.     confirmDataSource.Enabled = True
49.    
50. End Sub
51.  
52.  
53.  
54.  
55.  
56. Sub RemoveExistingCheckBoxes()
57.     Dim ctrl As MSForms.Control
58.    
59.     For Each ctrl In Me.Controls
60.         If TypeOf ctrl Is MSForms.CheckBox Then
61.             Me.Controls.Remove ctrl.Name
62.         End If
63.     Next ctrl
64. End Sub
65.  
66. Private Sub ComboBox3_Change()
67.     On Error GoTo ErrorHandler
68.  
69.     Debug.Print "Start ComboBox3_Change()"
70.  
71.     Dim wb1 As Workbook
72.     Dim wb2 As Workbook
73.     Dim ws1 As Worksheet
74.     Dim ws2 As Worksheet
75.     Dim lastCol1 As Integer
76.     Dim lastCol2 As Integer
77.     Dim col1 As Integer
78.     Dim colName As String
79.     Dim selectedSheet As String
80.     Dim columnSourceFound As Boolean
81.  
82.     Debug.Print "Getting selected sheet..."
83.     selectedSheet = ComboBox3.Value
84.     titleSheetTarget.Caption = selectedSheet
85.     titleSheetTargetString = titleSheetTarget.Caption
86.  
87.     If selectedSheet = "" Then Exit Sub ' Ki?m tra giá tr? h?p l? c?a selectedSheet
88.    
89.     Debug.Print "Opening workbooks..."
90.     Set wb1 = Workbooks.Open(filePathSource)
91.     Set wb2 = Workbooks.Open(filePathTarget)
92.  
93.     Debug.Print "Initializing worksheets..."
94.     Set ws1 = Nothing
95.     Set ws2 = Nothing
96.    
97.     For Each ws In wb1.Sheets
98.         If ws.Name = titleSheetSourceString Then
99.             Set ws1 = ws
100.             Exit For
101.         End If
102.     Next ws
103.    
104.     For Each ws In wb2.Sheets
105.         If ws.Name = titleSheetTargetString Then
106.             Set ws2 = ws
107.             Exit For
108.         End If
109.     Next ws
110.    
111.     If ws1 Is Nothing Or ws2 Is Nothing Then
112.         MsgBox "Failed to access sheets."
113.         Exit Sub
114.     End If
115.  
116.     Debug.Print "Getting last columns..."
117.     lastCol1 = ws1.Cells(1, ws1.Columns.Count).End(xlToLeft).Column
118.     lastCol2 = ws2.Cells(1, ws2.Columns.Count).End(xlToLeft).Column
119.    
120.     columnSourceFound = False
121.     Debug.Print "Removing existing checkboxes..."
122.     RemoveExistingCheckBoxes ' Xóa checkbox hi?n có
123.    
124.     For col1 = 1 To lastCol1
125.     colName = ws1.Cells(1, col1).Value
126.    
127.     Debug.Print "Processing column: " & col1 & ", Name: " & colName
128.    
129.     If WorksheetFunction.CountIf(ws2.Rows(1), colName) > 0 Then
130.         If colName = columnCheckFromSource Then columnSourceFound = True
131.        
132.         Debug.Print "Creating checkbox for column: " & col1 & ", Name: " & colName
133.        
134.         Dim newCheckbox As MSForms.CheckBox
135.         Set newCheckbox = Me.Controls.Add("Forms.CheckBox.1", "CheckBox_" & col1, True)
136.         With newCheckbox
137.             .Caption = colName
138.             .Value = False
139.             .Width = 100
140.             .Height = 18
141.             .Left = 335
142.             .Top = (col1 - 1) * 20 + 70 ' Ð?t giá tr? c?a Top d?a trên giá tr? c?a col1
143.            .Font.Size = 10
144.         End With
145.     End If
146. Next col1
147.  
148.     If columnSourceFound = False Then MsgBox "Not found an original column"
149.  
150.     Exit Sub
151.  
152. ErrorHandler:
153.     Debug.Print "Error: " & Err.Description
154.     Debug.Print "Error occurred at line: " & Erl
155.     HandleError Err.Description
156. End Sub
157.  
158.  
159. Sub HandleError(errorMessage As String)
160.     Dim errorLocation As String
161.     errorLocation = "Error occurred in at line " & Erl & vbCrLf
162.     MsgBox "Error: " & errorMessage & vbCrLf & errorLocation, vbCritical
163. End Sub
164.  
165.  
166.  
167.  
168. Private Sub Label1_Click()
169.    
170. End Sub
171.  
172. Private Sub CommandButton3_Click()
173.  
174.     ResetForm
175. End Sub
176.  
177. Private Sub ResetForm()
178.     ' Xóa d? li?u trong các ComboBox
179.    ComboBox1.Clear
180.     ComboBox2.Clear
181.     ComboBox3.Clear
182.    
183.     ' Reset các bi?n du?ng d?n
184.    filePathSource = ""
185.     filePathTarget = ""
186.     checkedValues = ""
187.     ' Reset các nhãn tiêu d?
188.    titleFilePathSource.Caption = ""
189.     titleFilePathTarget.Caption = ""
190.     titleSheetSource.Caption = ""
191.     titleSheetTarget.Caption = ""
192.    
193.     ' Vô hi?u hóa CommandButton2 và ComboBox3
194.    CommandButton2.Enabled = False
195.     ComboBox3.Enabled = False
196.    
197.     ' Vô hi?u hóa nút confirmDataSource
198.    confirmDataSource.Enabled = False
199.    
200.     ' Vô hi?u hóa các ô checkbox
201.    RemoveExistingCheckBoxes
202.    
203.     ' Vô hi?u hóa các di?u khi?n khác n?u c?n
204.    CommandButton1.Enabled = True
205.     ComboBox1.Enabled = True
206.     ComboBox2.Enabled = True
207. End Sub
208.  
209.  
210.  
211.  
212.  
213.  
214.  
215.  
216.  
217.  
218.  
219.  
220.  
221.  
222.  
223.  
224.  
225.  
226.  
227.  
228.  
229.  
230.  
231.  
232.  
233.  
234.  
235.  
236.  
237.  
238.  
239.  
240.  
241.  
242.  
243.  
244.  
245. Function FindColumn(ws As Worksheet, header As String) As Integer
246.     Dim i As Integer
247.    
248.     For i = 1 To ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
249.         If ws.Cells(1, i).Value = header Then
250.             FindColumn = i
251.             Exit Function
252.         End If
253.     Next i
254. End Function
255.  
256.  
257.  
258.  
259.  
260. Private Sub confirmDataSource_Click()
261.     CommandButton1.Enabled = False
262.    
263.     ComboBox1.Enabled = False
264.     ComboBox2.Enabled = False
265.    
266.     CommandButton2.Enabled = True
267.     ComboBox3.Enabled = True
268.    
269. End Sub
270.  
271. Private Sub sourceOriginalColumn_Click()
272.  
273. End Sub
274.  
275. Private Sub titleSheetSource_Click()
276.  
277. End Sub
278.  
279. Private Sub titleSheetTarget_Click()
280.  
281. End Sub
282.  
283. Private Sub UserForm_Click()
284.    
285. End Sub
286.  
287. Private Sub UserForm_Initialize()
288.     CommandButton2.Enabled = False
289.     ComboBox3.Enabled = False
290.     confirmDataSource.Enabled = False
291.    
292. End Sub
293.  
294. Function GetCheckedValues() As String
295.     Dim ctrl As Control
296.     Dim checkedValues As String
297.    
298.     ' Kh?i t?o chu?i
299.    checkedValues = ""
300.    
301.     ' Duy?t qua t?t c? các control trên form
302.    For Each ctrl In Me.Controls
303.         ' Ki?m tra n?u control là checkbox
304.        If TypeOf ctrl Is MSForms.CheckBox Then
305.             ' Ki?m tra n?u checkbox du?c dánh d?u True và không ph?i là checkbox có tên là columnCheckFromSource
306.            If ctrl.Value = True And ctrl.Caption <> columnCheckFromSource Then
307.                 ' Thêm tên c?a checkbox vào chu?i checkedValues
308.                checkedValues = checkedValues & ctrl.Caption & ","
309.             End If
310.         End If
311.     Next ctrl
312.    
313.     ' Lo?i b? d?u ',' cu?i cùng n?u có
314.    If Len(checkedValues) > 0 Then
315.         checkedValues = Left(checkedValues, Len(checkedValues) - 1)
316.     End If
317.    
318.     ' Tr? v? chu?i k?t qu?
319.    GetCheckedValues = checkedValues
320. End Function
321.  
322.  
323.  
324. Private Sub CommandButton1_Click()
325.  
326.     Dim fd As FileDialog
327.     Dim wb As Workbook
328.     Dim ws As Worksheet
329.     Dim lastCol As Integer
330.     Dim i As Integer
331.    
332.     Set fd = Application.FileDialog(msoFileDialogFilePicker)
333.    
334.     With fd
335.         .Title = "Ch?n File Excel"
336.         .AllowMultiSelect = False
337.         .Filters.Clear
338.         .Filters.Add "Excel Files", "*.xlsx; *.xls; *.xlsm"
339.         If .Show = -1 Then
340.          
341.             filePathSource = .SelectedItems(1)
342.             titleFilePathSource.Caption = filePathSource
343.            
344.            
345.             Set wb = Application.Workbooks.Open(filePathSource, UpdateLinks:=False, ReadOnly:=True)
346.            
347.            
348.             Dim sheetNames() As String
349.             ReDim sheetNames(1 To wb.Sheets.Count)
350.             For i = 1 To wb.Sheets.Count
351.                 sheetNames(i) = wb.Sheets(i).Name
352.             Next i
353.            
354.          
355.             With ComboBox1
356.                 .Clear
357.                 .List = sheetNames
358.             End With
359.            
360.          
361.         End If
362.     End With
363. End Sub
364.  
365. Private Sub CommandButton2_Click()
366.    Dim fd As FileDialog
367.     Dim wb As Workbook
368.     Dim ws As Worksheet
369.     Dim lastCol As Integer
370.     Dim i As Integer
371.    
372.     Set fd = Application.FileDialog(msoFileDialogFilePicker)
373.    
374.     With fd
375.         .Title = "Ch?n File Excel"
376.         .AllowMultiSelect = False
377.         .Filters.Clear
378.         .Filters.Add "Excel Files", "*.xlsx; *.xls; *.xlsm"
379.         If .Show = -1 Then
380.          
381.             filePathTarget = .SelectedItems(1)
382.             titleFilePathTarget.Caption = filePathTarget
383.            
384.            
385.        
386.    
387.             Set wb = Application.Workbooks.Open(filePathTarget, False, True) ' M? workbook không hi?n th?, ch? d?c
388.            
389.            
390.            
391.             Dim sheetNames() As String
392.             ReDim sheetNames(1 To wb.Sheets.Count)
393.             For i = 1 To wb.Sheets.Count
394.                 sheetNames(i) = wb.Sheets(i).Name
395.             Next i
396.            
397.          
398.             With ComboBox3
399.                 .Clear
400.                 .List = sheetNames
401.             End With
402.            
403.          
404.         End If
405.     End With
406. End Sub
407.  
408. Private Sub CommandButton4_Click()
409.    
410.    
411.     Dim ws1 As Worksheet
412.     Dim ws2 As Worksheet
413.     Dim lastRow1 As Long
414.     Dim lastRow2 As Long
415.     Dim i As Long, j As Long
416.     Dim searchColumn1 As Integer ' V? trí c?a c?t c?n tìm trong Sheet1
417.    Dim searchColumn2 As Integer ' V? trí c?a c?t c?n tìm trong Sheet2
418.  
419.     ' M? workbook ngu?n và workbook dích
420.    Set ws1 = Workbooks.Open(filePathSource).Sheets(titleSheetSource.Caption)
421.     Set ws2 = Workbooks.Open(filePathTarget).Sheets(titleSheetTarget.Caption)
422.    
423.     Debug.Print "Opened source workbook and sheet: " & filePathSource & ", " & titleSheetSource.Caption
424.     Debug.Print "Opened target workbook and sheet: " & filePathTarget & ", " & titleSheetTarget.Caption
425.  
426.    
427.     ' Tìm s? dòng cu?i cùng c?a m?i sheet
428.    lastRow1 = ws1.Cells(ws1.Rows.Count, "A").End(xlUp).Row
429.     lastRow2 = ws2.Cells(ws2.Rows.Count, "A").End(xlUp).Row
430.    
431.     Debug.Print "Last row in source sheet: " & lastRow1
432.     Debug.Print "Last row in target sheet: " & lastRow2
433.    
434.     ' Xác d?nh v? trí c?a c?t c?n tìm trong Sheet1 và Sheet2
435.    searchColumn1 = FindColumn(ws1, columnCheckFromSource)
436.     searchColumn2 = FindColumn(ws2, columnCheckFromSource)
437.     Debug.Print "Search column in source sheet: " & searchColumn1
438.     Debug.Print "Search column in target sheet: " & searchColumn2
439.    
440.     ' L?y danh sách các header du?c ch?n
441.    Dim headers As String
442.     Dim headerArray() As String
443.     Dim positionString As String
444.     Dim countString As String
445.     Dim cell As Range
446.    
447.     headers = GetCheckedValues()
448.     headerArray = Split(headers, ",")
449.    
450.     ' Tìm v? trí c?a các header trong Sheet1
451.    For i = LBound(headerArray) To UBound(headerArray)
452.         Set cell = ws1.Rows(1).Find(headerArray(i), LookIn:=xlValues, lookat:=xlWhole)
453.         If Not cell Is Nothing Then
454.             positionString = positionString & cell.Address & ","
455.             Debug.Print "Header '" & headerArray(i) & "' found in source sheet at position: " & cell.Address
456.         Else
457.             Debug.Print "Header '" & headerArray(i) & "' not found in source sheet."
458.         End If
459.     Next i
460.    
461.     ' Ki?m tra có tiêu d? nào không t?n t?i trong Sheet1 không
462.    If Len(positionString) = 0 Then
463.         Debug.Print "None of the selected headers found in source sheet. Exiting."
464.         Exit Sub
465.     End If
466.    
467.     ' Xóa d?u ph?y cu?i cùng
468.    positionString = Left(positionString, Len(positionString) - 1)
469.    
470.     ' S? lu?ng header
471.    countString = UBound(headerArray) - LBound(headerArray) + 1
472.    
473.     ' Tìm v? trí c?a các header trong Sheet2
474.    Dim positionStringTarget As String
475.     Dim countStringTarget As String
476.    
477.     For i = LBound(headerArray) To UBound(headerArray)
478.         Set cell = ws2.Rows(1).Find(headerArray(i), LookIn:=xlValues, lookat:=xlWhole)
479.         If Not cell Is Nothing Then
480.             positionStringTarget = positionStringTarget & cell.Address & ","
481.             Debug.Print "Header '" & headerArray(i) & "' found in target sheet at position: " & cell.Address
482.         Else
483.             Debug.Print "Header '" & headerArray(i) & "' not found in target sheet."
484.         End If
485.     Next i
486.    
487.     ' Ki?m tra có tiêu d? nào không t?n t?i trong Sheet2 không
488.    If Len(positionStringTarget) = 0 Then
489.         Debug.Print "None of the selected headers found in target sheet. Exiting."
490.         Exit Sub
491.     End If
492.    
493.     ' Xóa d?u ph?y cu?i cùng
494.    positionStringTarget = Left(positionStringTarget, Len(positionStringTarget) - 1)
495.    
496.     ' S? lu?ng header trong Sheet2
497.    countStringTarget = UBound(headerArray) - LBound(headerArray) + 1
498.    
499.     ' Chuy?n v? trí các header thành m?ng
500.    Dim positionsA() As String
501.     Dim positionsB() As String
502.    
503.     positionsA = Split(positionString, ",")
504.     positionsB = Split(positionStringTarget, ",")
505.    
506.     ' Copy d? li?u t? Sheet2 sang Sheet1
507.    ' Copy d? li?u t? Sheet2 sang Sheet1
508.    ' Copy d? li?u t? Sheet2 sang Sheet1
509.    searchColumn1 = FindColumn(ws1, columnCheckFromSource)
510.     searchColumn2 = FindColumn(ws2, columnCheckFromSource)
511.  
512. Dim foundRow As Range
513.  
514. ' L?p qua t?ng hàng trong t?p A
515. For i = 2 To lastRow1
516.     ' Tìm giá tr? tuong ?ng trong searchColumn2 c?a t?p B
517.    Set foundRow = ws2.Columns(searchColumn2).Find(ws1.Cells(i, searchColumn1).Value, LookIn:=xlValues, lookat:=xlWhole)
518.    
519.     ' N?u tìm th?y giá tr?
520.    If Not foundRow Is Nothing Then
521.         ' L?p qua t?ng v? trí c?n map trong Sheet1 và Sheet2
522.        For j = LBound(positionsA) To UBound(positionsA)
523.             ' Ki?m tra v? trí có h?p l? không
524.            If InStr(1, positionsA(j), "$") > 0 And InStr(1, positionsB(j), "$") > 0 Then
525.                 ' L?y ch? m?c c?t t? v? trí
526.                Dim columnA As Long
527.                 Dim columnB As Long
528.                 columnA = Range(positionsA(j)).Column
529.                 columnB = Range(positionsB(j)).Column
530.                
531.                 ' Gán giá tr? c?a ô t? Sheet2 sang Sheet1
532.                ws1.Cells(i, columnA).Value = ws2.Cells(foundRow.Row, columnB).Value
533.                 Debug.Print "Value copied from " & ws2.Name & " " & ws2.Cells(foundRow.Row, columnB).Address & " to " & ws1.Name & " " & ws1.Cells(i, columnA).Address
534.             Else
535.                 Debug.Print "Invalid position for header '" & headerArray(j) & "' in either source or target sheet."
536.             End If
537.         Next j
538.     Else
539.         Debug.Print "Value not found in " & ws2.Name & " for searchColumn2 value: " & ws1.Cells(i, searchColumn1).Value
540.     End If
541. Next i
542.  
543.     ws1.Parent.Save ' Luu ws1
544.  
545.     ' Luu workbook hi?n t?i
546.    ThisWorkbook.Save
547.  
548.     ' Ðóng workbook sau khi dã x? lý d? li?u
549.    ws1.Parent.Close False
550.     ws2.Parent.Close False
551.    
552.     Exit Sub ' Thoát kh?i hàm n?u không có l?i
553.    
554.  
555. End Sub
556.  
557.  
558.  
559.  
560.  
561.  
562. Function GenerateRandomSheetName() As String
563.     Dim sheetName As String
564.     Dim ws As Worksheet
565.     Dim nameExists As Boolean
566.    
567.     Randomize ' Kh?i t?o b? sinh s? ng?u nhiên
568.    
569.     ' L?p cho d?n khi tìm th?y m?t tên sheet chua du?c s? d?ng
570.    Do
571.         ' T?o tên sheet ng?u nhiên
572.        sheetName = "Sheet_" & Int((10000 * Rnd) + 1)
573.         nameExists = False ' Ð?t c? ki?m tra tên t?n t?i thành False
574.        
575.         ' Ki?m tra xem tên dã t?n t?i trong workbook chua
576.        For Each ws In ThisWorkbook.Sheets
577.             If ws.Name = sheetName Then
578.                 nameExists = True
579.                 Exit For ' Thoát kh?i vòng l?p khi tìm th?y tên dã t?n t?i
580.            End If
581.         Next ws
582.     Loop While nameExists ' L?p l?i quá trình n?u tên dã t?n t?i
583.    
584.     GenerateRandomSheetName = sheetName ' Tr? v? tên sheet ng?u nhiên
585. End Function
586.  
587.  
588.