|
- Sub cs()
- Dim d As Object, sh As Worksheet
- Dim i As Long, icol As Long, irow As Long, j As Long, col As Long, y As Long
- Dim arr, trr, trr2, brr()
- Set d = CreateObject("scripting.dictionary")
- With Sheets("X")
- icol = .Range("a1").End(2).Column
- irow = .Cells(Rows.Count, 1).End(3).Row
- trr = .Range("a1", .Cells(1, icol))
- arr = .Range("a2", .Cells(irow, icol + 1)) '多设一列用于容错
- End With
- For i = 1 To icol
- d(trr(1, i)) = i
- Next
- If d.Count > 0 Then
- For Each sh In ThisWorkbook.Sheets
- If sh.Name <> "X" Then
- ReDim brr(1 To irow - 1, 1 To icol)
- With sh
- col = .Range("a1").End(2).Column
- trr2 = .Range("a1", .Cells(1, col))
- For j = 1 To col
- If d.Exists(trr2(1, j)) Then '容错
- y = d(trr2(1, j))
- Else
- y = icol + 1
- End If
- For i = 1 To irow - 1
- brr(i, j) = arr(i, y)
- Next
- Next
- .Range("a2", .Cells(Rows.Count, Columns.Count)).ClearContents
- .Range("a2").Resize(irow - 1, col) = brr
- End With
- End If
- Next
- End If
- Set d = Nothing
- Set sh = Nothing
- End Sub
复制代码 |
|