VBA code required to amend specific pivot tables on a worksheet

Dobo_Bobo

New Member
Joined
Jan 24, 2018
Messages
31
Please can someone help me by tweaking tis code? I'm learning VBA code and I can't find a site that explains how to amend specific pivot tables on a worksheet. The code below will set all of the pivot tables on one sheet to the required 12 week range. But, I have another sheet where I only want to set the 12 week range for specific pivot tables. Thus, instead of 'For Each pt In ActiveSheet.PivotTables' I need the macro to amend PivotTable1 and PivotTable3 an ignore PivotTable2.

I still have a lot to learn about VBA so I don't know what to use instead of 'For Each pt In ActiveSheet.PivotTables'. If anyone can help me I will return to give thanks and I will sincerely appreciate your help!

Code:
Sub SetTwelveWeekRange()



Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
Dim StartWeek As Integer
Dim FinishWeek As Integer
'Dim begintwo As Integer '*This code needs to be switched on when the 10 week range covers the change of year.
'Dim finishtwo As Integer '*This code needs to be switched on when the 10 week range covers the change of year.

On Error GoTo Err
StartWeek = Range("P4")
FinishWeek = Range("P5")
'NYStartWeek = Range("r2") '*This code needs to be switched on when the 10 week range covers the change of year.
'NYFinishWeek = Range("r1") '*This code needs to be switched on when the 10 week range covers the change of year.

For Each pt In ActiveSheet.PivotTables


Set pf = pt.PivotFields("WkNo")

    pf.EnableMultiplePageItems = True



For Each pi In pf.PivotItems
    
    pi.Visible = pi >= StartWeek And pi <= FinishWeek 'Or pi >= NYStartWeek And pi <= NYFinishWeek

Next pi
Next pt
Err:
Resume Next
 



End Sub
 

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

Dobo_Bobo

New Member
Joined
Jan 24, 2018
Messages
31
EDIT: I know one option is to set the active field 'WkNo' only on the specific pivot tables I need amending, but I need learn if there is an alternaive to 'For Each pt...'.

I've tried
Code:
Set pt = ActiveSheet.Pivotables("PivotTable1", "PivotTable3")
but even with that code every pivot table is amended.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,118,358
Messages
5,571,710
Members
412,414
Latest member
KasunC
Top