- 帖子
- 2808
- 主題
- 10
- 精華
- 0
- 積分
- 2864
- 點名
- 0
- 作業系統
- 〔略〕
- 軟體版本
- 〔略〕
- 閱讀權限
- 100
- 性別
- 男
- 來自
- 〔略〕
- 註冊時間
- 2013-5-13
- 最後登錄
- 2024-5-10
|
16#
發表於 2024-1-2 14:17
| 只看該作者
＜排架表＞
Sub 拆分工作表()
Dim Arr, Brr, Drr, Xrr, Yrr, xD, xS As Worksheet, vS As Worksheet, R&, S$, T$, i&, j&, k%, x&, y%, N&
Call 刪除工作表
Set xD = CreateObject("Scripting.Dictionary")
Set vS = Sheets("排架表"): Xrr = vS.[a8:f8]
Arr = Range(vS.[b1], vS.[d65536].End(xlUp))
For i = 9 To UBound(Arr)
T = Arr(i, 2)
If T Like "SR####" Then xD(T) = vS.Cells(i, 2).MergeArea.Resize(, 5).Value
Next i
'-----------------------------
Set xS = Sheets("BF")
Arr = Range(xS.[f1], xS.[a65536].End(xlUp).MergeArea)
For i = 2 To UBound(Arr)
If Arr(i, 1) Like "BF工程[#]###*" Then
S = Mid(Arr(i, 1), 5, 4): N = 0
Brr = xS.Cells(i, 1).MergeArea.Resize(, 5).Value
ReDim Yrr(1 To 2000, 1 To 6)
N = N + 1
For y = 1 To 6: Yrr(N, y) = Xrr(1, Mid(123645, y, 1)): Next
For j = 1 To UBound(Brr)
For k = 2 To UBound(Brr, 2)
If Brr(j, k) Like "*架*SR####*" Then
T = Mid(Brr(j, k), 4, 6)
Drr = xD(T)
If IsArray(Drr) Then
For x = 1 To UBound(Drr)
N = N + 1
Yrr(N, 1) = "=row()-1"
Yrr(N, 2) = Drr(1, 1)
Yrr(N, 3) = Drr(1, 2)
Yrr(N, 4) = Drr(1, 5)
Yrr(N, 5) = Drr(1, 3)
Yrr(N, 6) = Drr(1, 4)
Next x
End If
End If
Next k
Next j
'-----------------------------------
If N <= 1 Then GoTo i01
Set vS = Sheets.Add(after:=vS): vS.Name = S
With vS.[a1].Resize(N, 6)
.Value = Yrr
.Borders.LineStyle = 1
.Sort Key1:=.Item(3), Order1:=xlAscending, _
Key2:=.Item(4), Order2:=xlAscending, _
Key3:=.Item(2), Order3:=xlAscending, Header:=xlYes
T = "'" & S & "'!" & .Address
End With
'-----------------------------------
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=T).CreatePivotTable TableDestination:=vS.Range("i1"), TableName:="Pvt_1"
vS.PivotTables("Pvt_1").AddFields RowFields:=vS.Range("C1"), ColumnFields:=vS.Range("d1")
vS.PivotTables("Pvt_1").PivotFields(vS.Range("F1").Text).Orientation = xlDataField
End If
Application.CommandBars("PivotTable").Visible = False
i01: Next i
End Sub
Sub 刪除工作表()
Dim xS As Worksheet
Application.DisplayAlerts = False
For Each xS In Sheets
If xS.Name Like "[#]###" Then xS.Delete
Next
End Sub |
|
發表於 2023-12-20 11:57
| 
