利用VB宏设置将多个excel表合并为一个

多个excel合并成一个excel文件-应用哥科技

Sub MergeExcelFiles()
‘声明变量
Dim MyFile As Variant
Dim MySheet As Worksheet, MySheet1 As Worksheet
Dim LastRow As Long, LastRow1 As Long
Dim NextRow As Long
‘设置初始值
NextRow = 1
‘打开多个Excel文件
MyFile = Application.GetOpenFilename(FileFilter:=”Excel files (*.xls*), *.xls*”, Title:=”Select files to merge”, MultiSelect:=True)
If TypeName(MyFile) = “Boolean” Then Exit Sub
‘遍历每个Excel文件
For Each File In MyFile
Workbooks.Open Filename:=File
Set MySheet = ActiveWorkbook.Sheets(1)
‘复制数据
LastRow = MySheet.Cells(Rows.Count, “A”).End(xlUp).Row
MySheet.Range(“A1:Z” & LastRow).Copy
ActiveWorkbook.Close savechanges:=False
‘粘贴数据
Set MySheet1 = ThisWorkbook.Sheets(1)
LastRow1 = MySheet1.Cells(Rows.Count, “A”).End(xlUp).Row
MySheet1.Range(“A” & LastRow1 + 1).PasteSpecial xlPasteValues
‘更新行号
NextRow = MySheet1.Cells(Rows.Count, “A”).End(xlUp).Row + 1
Next
End Sub