|
- Sub tt()
- Dim sht As Worksheet, p$, f$, d As Object, arr, brr, i&, j&, k&
- Application.ScreenUpdating = False
- Application.DisplayAlerts = False
- Set d = CreateObject("scripting.dictionary")
- For Each sht In Worksheets
- If sht.Name <> ActiveSheet.Name Then sht.Delete
- Next
- Set sh = ActiveSheet
- ReDim brr(1 To Cells.Rows.Count, 1 To 20)
- p = ThisWorkbook.Path & ""
- f = Dir(p & "*.xls")
- Do While f <> ""
- If f <> ThisWorkbook.Name Then
- With GetObject(p & f)
- For Each sht In .Worksheets
- shtname = sht.Name
- arr = sht.[a1].CurrentRegion
- For i = 2 To UBound(arr)
- k = k + 1
- For j = 1 To UBound(arr, 2)
- brr(k, j) = arr(i, j)
- Next
- brr(k, UBound(arr, 2) + 1) = Split(f, ".xls")(0)
- If Not d.exists(shtname) Then
- Set d(shtname) = CreateObject("scripting.dictionary")
- For n = 1 To UBound(arr, 2)
- s = s & "," & arr(1, n)
- Next
- d(shtname)(shtname) = Mid(s, 2)
- s = ""
- End If
- d(shtname)(k) = ""
- Next
- Next
- .Close False
- End With
- End If
- f = Dir()
- Loop
- kr = d.keys
- For i = 0 To UBound(kr)
- With Worksheets.Add(, Sheets(Sheets.Count))
- .Name = kr(i)
- r = d(kr(i)).keys
- ReDim drr(1 To UBound(r) + 1, 1 To UBound(brr, 2))
- For x = 0 To UBound(r)
- If r(x) <> kr(i) Then
- For y = 1 To UBound(brr, 2)
- drr(x, y) = brr(r(x), y)
- Next
- End If
- Next
- ar = Split(d(kr(i))(kr(i)), ",")
- .[a1].Resize(1, UBound(ar) + 1) = ar
- .[a2].Resize(UBound(r) + 1, UBound(brr, 2)) = drr
- End With
- Next
- sh.Activate
- Set d = Nothing
- Application.ScreenUpdating = True
- Application.DisplayAlerts = True
- End Sub
复制代码
|
|