2、解决原来文件名带括号引起的BUG
=================================
Sub 动态透视表 ()
Application.DisplayAlerts = False
Set abb = ActiveSheet
Set fanwei = Selection.CurrentRegion ‘扩展范围
aa = fanwei.Range(“a1”).Row
bb = fanwei.Range(“a1”).Column
Set ab = fanwei.Cells(1, 1) ‘找出第一个单元格
lastn = ab.Address(ReferenceStyle:=x1R1C1, External:=True)
ca = Application.WorksheetFunction.CountA(Rows(aa))
cb = Application.WorksheetFunction.CountA(Columns(bb))
fd = “=OFFSET(‘” & abb.Name & “‘!” & lastn & “,0,0,” & cb & “,” & ca & “)”
ActiveWorkbook.Names.Add Name:=”数据源”, RefersToR1C1:=fd ‘创建动态数据源
Set Pivot = Sheets.Add
‘ On Error Resume Next
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
“数据源”, Version:=6).CreatePivotTable TableDestination:= _
Pivot.Name & “!R3C1″, TableName:=”数据透视表” & n, DefaultVersion:=6 ‘新建透视表,并命名透视表名为表n
With ActiveSheet.PivotTables(“数据透视表”).PivotFields(ab.Value)
.Orientation = xlRowField
.Position = 1
End With
arr = Application.Transpose([{“”;1;2;3;4;5;6;7;8;9}])
n = 1
For Each Rng In Sheets
addname = “透视” & arr(n)
If Rng.Name = addname Then n = n + 1
Pivot.Name = “透视” & arr(n)
Next
[a3].Select
Application.DisplayAlerts = True
End Sub
