|
- Sub 自动生成明细表()
- Dim arr, arr1(1 To 10000, 1 To 17), arrx
- Dim i, j, d, sh, intarr, l, k
- Application.ScreenUpdating = False
- Application.DisplayAlerts = False
- On Error Resume Next
- Set d = CreateObject("Scripting.dictionary")
- For Each sh In Worksheets
- If sh.Name <> "明细分类账" And sh.Name <> "目录" Then
- sh.Delete
- End If
- Next
- arr = Sheets("明细分类账").Range("B2:B" & Cells(Rows.Count, 2).End(3).Row)
- arrx = Sheets("明细分类账").Range("A2:Q" & Cells(Rows.Count, 2).End(3).Row)
- For i = 1 To UBound(arr)
- d(arr(i, 1)) = ""
- Next i
- For j = 1 To d.Count
- Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = Application.Index(d.keys, j)
- Sheets("明细分类账").Range("A1:Q1").Copy Sheets(Application.Index(d.keys, j)).Range("A1")
- Next j
- For j = 1 To d.Count
- For k = 1 To UBound(arr1)
- If arrx(k, 2) = Application.Index(d.keys, j) Then
- l = l + 1
- For intarr = 1 To 17
- arr1(l, intarr) = arrx(k, intarr)
- Next intarr
- End If
- Next k
- Sheets(Application.Index(d.keys, j)).Range("A2").Resize(l, 17) = arr1
- Sheets(Application.Index(d.keys, j)).Range("A:A").EntireColumn.Hidden = True
- l = 0
- Next j
- Application.DisplayAlerts = True
- Application.ScreenUpdating = True
- End Sub
复制代码 我也写一个 |
|