- ©«¤l
- 438
- ¥DÃD
- 67
- ºëµØ
- 0
- ¿n¤À
- 531
- ÂI¦W
- 30
- §@·~¨t²Î
- win7
- ³nÅ骩¥»
- office 2010
- ¾\ŪÅv
- 50
- ©Ê§O
- ¨k
- µù¥U®É¶¡
- 2012-10-30
- ³Ì«áµn¿ý
- 2024-10-8
|
¦^´_ 12# ã´£³¡ªL
¦]爲Ác²Åé°ÝÃD¡A¾ÉP¶Ã½X¡A¾ÉPµLªk¹B¦æ¡C ¦]爲¨S¦³ì¤å¡A§ÚµLªk×¥¿¶Ã½X³¡¤À¡C¥i§_µo¤@¤Uì¤åµ¹§Ú×¥¿¡C
Sub ╊ÇG()
Attribute ╊ÇG.VB_Description = "Å÷场Ò§ 2024/1/1 »íÐFǤ栋"
Attribute ╊ÇG.VB_ProcData.VB_Invoke_Func = " \n14"
Dim Arr, Brr, Drr, Xrr, Yrr, xD, xS As Worksheet, vS As Worksheet, R&, S$, T$, i&, j&, k%, x&, y%, N&
Call ®J
Set xD = CreateObject("Scripting.Dictionary")
Set vS = Sheets("¹G琜"): 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 ®J()
Dim xS As Worksheet
Application.DisplayAlerts = False
For Each xS In Sheets
If xS.Name Like "[#]###" Then xS.Delete
Next
End Sub
Sub ttt()
MsgBox Val("11 22")
End Sub
Sub Macro2()
'
' Macro2 Ǥ栋
' Å÷场Ò§ 2024/1/1 »íÐFǤ栋
'
'
' Columns("B:F").Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:="'#001'!C1:f86").CreatePivotTable TableDestination:=Range("i1"), TableName:="ÏEÇG猂1"
'ActiveSheet.PivotTables("ÏEÇG猂1").SmallGrid = False
ActiveSheet.PivotTables("ÏEÇG猂1").AddFields RowFields:=Range("C1"), ColumnFields:=Range("d1")
ActiveSheet.PivotTables("ÏEÇG猂1").PivotFields(Range("F1").Text).Orientation = xlDataField
End Sub |
|