|
本帖最后由 hwc2ycy 于 2012-10-23 00:11 编辑
- Sub 分类工作簿()
- Dim dic As Object
- Dim i&, j&, iQY, k&
- Dim arr
- Dim arrTemp
- Dim arrHead
- Dim sPath$, sFilename$
- '关闭屏幕刷新
- Application.ScreenUpdating = False
- '关闭警告信息和提示,对于保存文件来说,小心文件覆盖。
- Application.SheetsInNewWorkbook = 1
- Application.DisplayAlerts = False
- Set dic = CreateObject("scripting.dictionary")
- arrHead = Range(Cells(1, 1), Cells(1, Range("A1").End(xlToRight).Column))
- sPath = ThisWorkbook.Path & Application.PathSeparator
- '取工作表数据
- arr = Range("a1").CurrentRegion
- For i = 2 To UBound(arr)
- iQY = arr(i, 1) '区域
- If Not dic.exists(iQY) Then
- ReDim arrTemp(1 To 3, 1 To 1)
- For j = 1 To UBound(arrTemp) '
- arrTemp(j, 1) = arr(i, j)
- Next
- dic.Add iQY, arrTemp
- Else
- arrTemp = dic(iQY)
- k = UBound(arrTemp, 2) + 1
- ReDim Preserve arrTemp(1 To 3, 1 To k)
- For j = 1 To UBound(arrTemp)
- arrTemp(j, k) = arr(i, j)
- Next
- dic(iQY) = arrTemp
- Erase arrTemp
- End If
- Next
- 'Erase arrTemp
- For Each iQY In dic.keys
- arrTemp = dic(iQY)
- Workbooks.Add
- Range("a1").Resize(, UBound(arrHead, 2)) = arrHead
- Range("a2").Resize(UBound(arrTemp, 2), UBound(arrTemp)) = WorksheetFunction.Transpose(arrTemp)
- Range("a1").CurrentRegion.Columns.AutoFit
- ActiveSheet.Name = iQY
- sFilename = sPath & iQY & ".xls"
- ActiveWorkbook.SaveAs Filename:=sFilename, FileFormat:=xlExcel7
- ActiveWorkbook.Close
- Next
- MsgBox "数据导出完成"
- End Sub
复制代码 |
|