excel不同文件表格批量加表头vba_Excel VBA 两个工作表按相同表头导出数据

双击该按钮进入VBA界面，输入以下代码：

Private Sub CommandButton1_Click()

Dim target As Workbook

Dim path As String

path = ThisWorkbook.path

Set target = Workbooks.Open(Filename:=path & "\" & "test.xlsx") '打开比较的工作表

Dim grid1, grid2 As Range '定义两个区域

For Each grid1 In ThisWorkbook.Sheets(1).Range(Cells(1, 1).Address, Cells(1, Cells(1, Columns.Count).End(xlToLeft).Column).Address) '区域1是本表中第一行所有非空表头数据

For Each grid2 In target.Sheets(1).Range(Cells(1, 1).Address, Cells(1, Cells(1, Columns.Count).End(xlToLeft).Column).Address) '区域2是目标表中第一行所有非空表头数据

If grid1 = grid2 Then  '如果两个表头相同

ThisWorkbook.Sheets(1).Range(Cells(2, grid1.Column).Address, Cells(Cells(Rows.Count, grid1.Column).End(xlUp).Row, grid1.Column).Address).copy    '复制本表该表头列下的所有数据

target.Sheets(1).Range(Cells(target.Sheets(1).Cells(Rows.Count,