excel sheet合并 宏初学

客户提出需求，要求导出表各sheet页之间数据做关联，比如

sheet1 sheet2

A     B  C  D

1  a  1      f

2  b  3     dd

3  r  2      ge

4  rr

合并成一个sheet，条件为A = C

Private Sub merge(left, right)
    
    Set sh = Worksheets.Add(after:=Worksheets(Worksheets.Count))
    sh.Name = left.Name & " AND " & right.Name
    
    Dim rowLeft As Long
    Dim rowRight As Long
    Dim colLeft As Long
    Dim colRight As Long
    Dim row As Long
    
    rowLeft = left.UsedRange.Rows.Count
    rowRight = right.UsedRange.Rows.Count
    colLeft = left.UsedRange.Columns.Count
    colRight = right.UsedRange.Columns.Count
    row = 1
        
    For i = 1 To rowLeft:
        For j = 1 To rowRight:
            If left.Cells(i, 1).Value = right.Cells(j, 1) Then
                For k = 1 To colLeft + colRight:
                    If k > colLeft Then
                        sh.Cells(j, k) = right.Cells(j, k - colLeft)
                    Else
                        sh.Cells(j, k) = left.Cells(i, k)
                    End If
                Next
                row = row + 1
            End If
        Next
    Next
End Sub


Sub Workbook_open()
    Call merge(Sheets("sheet1"), Sheets("sheet2"))
End Sub