在处理大量Excel数据时，工作中我们经常需要将多个工作表的数据合并到一个工作表中，并且确保相同的标题只出现在目标工作表中的一列，如果工作表数量少，复制粘贴可以，如果数据很庞大，就会花费很多时间。
步骤一：启用并打开VBA编辑器
在Excel中：
- 启用开发工具选项卡：
- 点击 文件 -> 选项 -> 自定义功能区。
- 在右侧的主选项卡下，勾选开发工具，然后点击确定。
- 打开VBA编辑器：
- 按 Alt + F11 打开VBA编辑器。
- 在VBA编辑器中，点击 插入 -> 模块，插入一个新模块。
在WPS Office中：
- 启用开发者工具：
- 如果你从未使用过VBA，需要先下载VB插件，然后启用开发者工具。点击开始 -> 选项 -> 功能区设置。
- 勾选开发工具，然后点击确定。
- 打开VBA编辑器：
- 按 Alt + F11 打开VBA编辑器。
- 在VBA编辑器中，点击 插入 -> 模块，插入一个新模块。
步骤二：粘贴并修改代码
将以下代码粘贴到新模块中：
Sub MergeSheetsWithHeaders()
Dim ws As Worksheet
Dim targetSheet As Worksheet
Dim headers As Object
Dim lastRow As Long
Dim r As Long, c As Long
Dim targetRow As Long, targetCol As Long
Dim headerValue As String
' 初始化字典来存储标题和对应的列号
Set headers = CreateObject("Scripting.Dictionary")
' 创建或清理目标工作表
On Error Resume Next
Set targetSheet = ThisWorkbook.Sheets("汇总表")
If targetSheet Is Nothing Then
Set targetSheet = ThisWorkbook.Worksheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
targetSheet.Name = "汇总表"
Else
targetSheet.Cells.Clear
End If
On Error GoTo 0
' 参数修改区域
Const X As Integer = 1 ' 数据列的起始列号，例如A列对应的列号是1
Const Y As Integer = 14 ' 数据列的结束列号，例如N列对应的列号是14
Const HEADER_ROW As Integer = 8 ' 标题所在的行号
Const DATA_START_ROW As Integer = 9 ' 数据开始的行号
' 首先遍历所有工作表来收集X到Y列的标题
For Each ws In ThisWorkbook.Worksheets
If ws.Name <> targetSheet.Name Then
For c = X To Y ' X到Y列对应的列号
headerValue = ws.Cells(HEADER_ROW, c).Value
If Not headers.Exists(headerValue) Then
headers.Add headerValue, headers.Count + 1
targetSheet.Cells(1, headers.Count).Value = headerValue ' 在目标工作表中写入标题
End If
Next c
End If
Next ws
' 然后再次遍历所有工作表来填充数据
For Each ws In ThisWorkbook.Worksheets
If ws.Name <> targetSheet.Name Then
lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
For r = DATA_START_ROW To lastRow ' 数据从DATA_START_ROW行开始
targetRow = targetSheet.Cells(targetSheet.Rows.Count, 1).End(xlUp).Row + 1
For c = X To Y ' 只处理X到Y列
headerValue = ws.Cells(HEADER_ROW, c).Value
If headers.Exists(headerValue) Then
targetCol = headers(headerValue)
targetSheet.Cells(targetRow, targetCol).Value = ws.Cells(r, c).Value
End If
Next c
Next r
End If
Next ws
MsgBox "合并完成！"
End Sub
步骤三：修改参数
根据你的数据结构修改以下参数：
- X - 数据列的起始列号。例如，如果数据从A列开始，X可以设置为1。
- Y - 数据列的结束列号。例如，如果数据到N列结束，Y可以设置为14。
- HEADER_ROW - 标题所在的行号。例如，如果标题在第8行，HEADER_ROW可以设置为8。
- DATA_START_ROW - 数据开始的行号。例如，如果数据从第9行开始，DATA_START_ROW可以设置为9。
示例如下：
Const X As Integer = 1 ' 数据列的起始列号，例如A列对应的列号是1
Const Y As Integer = 14 ' 数据列的结束列号，例如N列对应的列号是14
Const HEADER_ROW As Integer = 8 ' 标题所在的行号
Const DATA_START_ROW As Integer = 9 ' 数据开始的行号步骤四：运行代码
- 在VBA编辑器中，点击 运行 按钮或者按 F5 运行这段代码。
- 程序将自动遍历所有工作表，并将数据合并到名为“汇总表”的工作表中。
- 合并完成后，会弹出消息框提示“合并完成！”
总结
通过这段简单的VBA代码，可以轻松地将多个工作表的数据合并到一个工作表中，并保持标题的一致性。关注我，带你学习更多有创造性的知识！！！
评论 (0)