- ©«¤l
- 2824
- ¥DÃD
- 10
- ºëµØ
- 0
- ¿n¤À
- 2880
- ÂI¦W
- 0
- §@·~¨t²Î
- ¡e²¤¡f
- ³nÅ骩¥»
- ¡e²¤¡f
- ¾\ŪÅv
- 100
- ©Ê§O
- ¨k
- ¨Ó¦Û
- ¡e²¤¡f
- µù¥U®É¶¡
- 2013-5-13
- ³Ì«áµn¿ý
- 2024-8-3
|
¡Õ±Æ¬[ªí¡Ö
Sub ©î¤À¤u§@ªí()
Dim Arr, Brr, Drr, Xrr, Yrr, xD, xS As Worksheet, vS As Worksheet, R&, S$, T$, i&, j&, k%, x&, y%, N&
Call §R°£¤u§@ªí
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¤uµ{[#]###*" 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 §R°£¤u§@ªí()
Dim xS As Worksheet
Application.DisplayAlerts = False
For Each xS In Sheets
If xS.Name Like "[#]###" Then xS.Delete
Next
End Sub |
|