|
- Sub 生成工作表()
- Dim Arr, Arr1, Arr2(1 To 10000, 1 To 28)
- Dim i%, j%, k%, Intarr, Tx
- Dim Dic
- Dim Sh
- Application.ScreenUpdating = False
- Application.DisplayAlerts = False
- On Error Resume Next
- For Each Sh In Worksheets
- If Sh.Name <> "Sheet1" Then
- Sh.Delete
- End If
- Next
- Arr = Sheets("Sheet1").Range("A2:A" & Cells(Rows.Count, 1).End(3).Row)
- Arr1 = Sheets("Sheet1").Range("a2:ab" & Cells(Rows.Count, 1).End(3).Row)
- Set Dic = CreateObject("scripting.dictionary")
- For i = 1 To UBound(Arr)
- Dic(Arr(i, 1)) = ""
- Next i
- For j = 1 To Dic.Count
- Sheets.Add(after:=Worksheets(Worksheets.Count)).Name = Application.Index(Dic.keys, j)
- Sheets("Sheet1").Range("A1:AB1").Copy Sheets(Application.Index(Dic.keys, j)).Range("A1")
- Sheets(Application.Index(Dic.keys, j)).Range("1:1").RowHeight = 70
- Next j
- For j = 1 To Dic.Count
- For k = 1 To UBound(Arr1)
- If Arr1(k, 1) = Application.Index(Dic.keys, j) Then
- Tx = Tx + 1
- For Intarr = 1 To 28
- Arr2(Tx, Intarr) = Arr1(k, Intarr)
- Next Intarr
- End If
- Next k
- Sheets(Application.Index(Dic.keys, j)).Range("a2").Resize(k, 28) = Arr2
- Tx = 0
- Next j
- Call 拆分工作簿
- ActiveWindow.Close
- Application.ScreenUpdating = True
- Application.DisplayAlerts = True
- End Sub
- '>分割线================================================================================================================================分割线
- Sub 拆分工作簿()
- Dim i, mbook As Workbook
- Application.ScreenUpdating = False
- Set mbook = ActiveWorkbook
- For i = 1 To Sheets.Count
- mbook.Sheets(i).Copy
- ActiveWorkbook.SaveAs ThisWorkbook.Path & "" & mbook.Sheets(i).Name & ".xls"
- ActiveWindow.Close
- Next
- Application.ScreenUpdating = True
- End Sub
复制代码 复制吧,应该是你要的效果了{:1012:} |
|