1、可以規(guī)避透視表新增數(shù)據(jù)源行后，透視表無法刷新到新區(qū)域

2、解決原來文件名帶括號(hào)引起的BUG

=================================

Sub 動(dòng)態(tài)透視表 ()

Application.DisplayAlerts = False

Set abb = ActiveSheet

Set fanwei = Selection.CurrentRegion ‘擴(kuò)展范圍

aa = fanwei.Range(“a1”).Row

bb = fanwei.Range(“a1”).Column

Set ab = fanwei.Cells(1, 1) ‘找出第一個(gè)單元格

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:=”數(shù)據(jù)源”, RefersToR1C1:=fd ‘創(chuàng)建動(dòng)態(tài)數(shù)據(jù)源

Set Pivot = Sheets.Add

‘ On Error Resume Next

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _

“數(shù)據(jù)源”, Version:=6).CreatePivotTable TableDestination:= _

Pivot.Name & “!R3C1″, TableName:=”數(shù)據(jù)透視表” & n, DefaultVersion:=6 ‘新建透視表,并命名透視表名為表n

With ActiveSheet.PivotTables(“數(shù)據(jù)透視表”).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