|
- Sub 提取1()
- '---------------------------------------------------------------------------------------
- ' Procedure : 提取1
- ' Author : hwc2ycy
- ' Date : 2013/1/29
- ' Purpose :所有数据再分列
- '---------------------------------------------------------------------------------------
- '
- Dim ShtArr, arr(1 To 6, 1 To 1)
- Dim iRow&, i&, j&, k&, iRow2&, iCol&
- Dim shtMerge1$, DestShtArr
- Dim shtPos&
- shtMerge1 = "合并"
- ShtArr = Array("主表格", "主表格1", "主表格2", "主表格3", "主表格4", "主表格5")
- DestShtArr = Array("合并", "合并1", "合并2", "合并3", "合并4")
- Application.ScreenUpdating = False
- With Worksheets(ShtArr(0))
- iRow = .Cells(Rows.Count, "au").End(xlUp).Row
- iCol = .Cells(16, Columns.Count).End(xlToLeft).Column
- End With
- For k = 47 To 55 Step 2
- Worksheets(DestShtArr(shtPos)).Columns("j").Clear
- For i = 16 To iRow
- Erase arr
- For j = 0 To UBound(ShtArr)
- arr(j + 1, 1) = Worksheets(ShtArr(j)).Cells(i, k)
- If Len(arr(j + 1, 1)) = 0 Then arr(j + 1, 1) = " "
- Next j
- With Worksheets(DestShtArr(shtPos))
- iRow2 = .Cells(Rows.Count, "j").End(xlUp).Row + 1
- If iRow2 < 6 Then iRow2 = 6
- '.Cells(iRow2, "j").Resize(6) = arr
- For Each arritem In arr
- If Len(arritem) Then
- arrtemp = Split(arritem, " ")
- .Cells(iRow2, "j").Resize(, UBound(arrtemp) + 1) = arrtemp
- Else
- .Cells(iRow2, "j") = arritem
- End If
- iRow2 = iRow2 + 1
- Next
- .Columns("j").AutoFit
- .Columns("j").HorizontalAlignment = xlLeft
- End With
- Next i
- shtPos = shtPos + 1
- Next k
- Application.ScreenUpdating = True
- MsgBox "提取完成"
- End Sub
复制代码 |
|