set pivottable source data to range name

[PBcorn]

I have a pivottable which i need to link to a range name set within a macro,
i.e. macro runs, sets range variable, pivottable sourcedata equals this,
refresh pivottable.

what is the best way of doing this as i can't seem to do it without running
the wizard?

 

[Matthew Herbert]

PBCorn,

There is some sample code that will generate a PivotTable on a worksheet. I
don't know where you want your pivot table or where your pivot table data is
located, so you'll need to adjust the code accordingly. The code was
generated in Office 2007.

Best,

Matthew Herbert

Dim Wkb As Workbook
Dim Wks As Worksheet
Dim pvtCache As PivotCache
Dim pvtTbl As PivotTable
Dim rngSourceData As Range

'you can use .CurrentRegion or combination of .End
'to set the source data range
Set rngSourceData = Worksheets("XYZ").Range("A1:C10")
Set Wks = Worksheets.Add
Set Wkb = Wks.Parent

With Wkb
Set pvtCache = .PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
rngSourceData, Version:=xlPivotTableVersion12)

Set pvtTbl = pvtCache.CreatePivotTable(TableDestination:=Wks.Range("A1"))

With pvtTbl
With .PivotFields("Date")
.Orientation = xlRowField
.Position = 1
End With

With .PivotFields("File Name")
.Orientation = xlColumnField
.Position = 1
End With

.AddDataField .PivotFields("Value"), "Sum of Value", xlSum
.RowGrand = False
.ColumnGrand = False
End With

.ShowPivotTableFieldList = False
End With