|
Sub merged01()
Dim mypath As String
Dim filepath As String
Dim wb As Workbook, rng As Range, rngs As Range
Dim n As Integer, arr, arr1, a
Dim sht As Worksheet, path01 As String
On Error Resume Next
Application.ScreenUpdating = False
Sheets("合并").Cells.ClearContents
Set rngs = ThisWorkbook.Sheets("要求").Range("a2:a11")
mypath = ThisWorkbook.Path
filepath = Dir(mypath & "\*" & ".xl*")
Debug.Print ThisWorkbook.Name
Do
If Split(ThisWorkbook.Name, ".")(0) = Split(filepath, ".")(0) Then
GoTo 100
Else
For Each rng In rngs
If rng.Value = Split(filepath, ".")(0) Then
arr = Split(rng.Offset(0, 1), ",")
arr1 = Split(rng.Offset(0, 2), ",")
If UBound(arr) <> UBound(arr1) Then
MsgBox rng.Address(0, 0) & "旁边B,C列对应位置数量不一致"
Exit Sub
Else
Set wb = Application.Workbooks.Open(mypath & "\" & filepath)
For Each sht In wb.Worksheets
If wb.Sheets.Count <> 1 Then
MsgBox "当前文档超过不等于1个工作表,请修改" & wb.Name
wb.Close
Exit Sub
End If
ThisWorkbook.Activate
For n = LBound(arr) To UBound(arr)
Sheets("合并").Range(arr1(n)) = sht.Range(arr(n)).Value
Next
Next
End If
wb.Close
Exit For
End If
Next
End If
100:
filepath = Dir
Loop Until filepath = ""
MsgBox "合并完成"
End Sub
请将合并文件放在一个文件夹里。 |
|