Writing Macro to Refresh multiple pivot tables with a button

Hi Kay,

I have received the file, but there is no data or PivotTables or macros in the workbook. I cannot find out what the problem is without having this in the workbook.

Run Time error 1004 can be a lot of things. You have an error in your workbook otherwise the code would have worked and you wouldn't have got the error message.

You ask how Pivot data is selected using VBA. Pivot data are stored in cache in your computer's memory. Every time you open the workbook Excel will create a "space" in your computer's memory for the pivot data.

The code below is an example how to create PivotTable from VBA. I have added some explanations inside the code:

Sub MakePivotTable()

Dim pt As PivotTable 'declare pivot table object as declaring variable
Dim PvtCache As PivotCache 'holds the data from the source. pivot cache=data
Dim pf As PivotField
Dim pi As PivotItem 'each cell content the data under each pivot field

On Error Resume Next
Sheets("Pivot").Select
ActiveSheet.PivotTables("MyPT").TableRange2.Clear 'deletes the Pivot table

'set the cache of pt

Sheets("data").Select
Set PvtCache = ActiveWorkbook.PivotCaches.Create(xlDatabase, Range("Data")) 'Data is a range name
Sheets("Pivot").Select
Set pt = ActiveSheet.PivotTables.Add(PvtCache, Range("a1"), "MyPT")
With pt
'''''add fields
.PivotFields("Date").Orientation = xlRowField
'.PivotFields("Name").Orientation = xlColumnField
.PivotFields("Product").Orientation = xlRowField
.PivotFields("Price").Orientation = xlDataField 'values
'set the number format
.DataBodyRange.NumberFormat = "£ #,##0.00"
'classic pivot table view
.RowAxisLayout xlTabularRow
'add calculated field
'.CalculatedFields.Add "Commission", "=Price*.05"
'.PivotFields("Commission").Orientation = xlDataField

'add name to page field
.PivotFields("Name").Orientation = xlPageField

End With


Set pf = pt.PivotFields("Name")
With pf
For Each pi In pf.PivotItems
If pi.Name = "James" Or pi.Name = "Bob" Then
pi.Visible = True
Else
pi.Visible = False
End If
Next pi
End With

Set pf = pt.PivotFields("Product")
With pf
For Each pi In pf.PivotItems
If pi.Name = "Ball" Then
pi.Visible = True
Else
pi.Visible = False
End If
Next pi
End With


End Sub
Sub ChangeToPercent()
With ActiveSheet.PivotTables("MyPT")
.DataFields(1).Calculation = xlPercentOfColumn
.DataBodyRange.NumberFormat = "0.00%"
End With


End Sub
Sub ChangeToCount()


With ActiveSheet.PivotTables("MyPT")
.DataFields(1).Calculation = xlNormal
.DataFields(1).Function = xlCount
.DataBodyRange.NumberFormat = "0"

End With
End Sub





Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector