VBA to Drill Down on Pivot Table and Rename Sheets

[welshraz]

Hello,

I have a small pivot table with data on various UK regions. Each week I need to drill down on each of the regions and rename each of the new tabs with the specified region name. There are several other pivot tables on the sheet, but only one will have this process attached to it. The region names are different every week, but there will be no more than 10 at a time.

I am doing this manually at the moment, but would like to add it on to an existing automation. I'm having trouble because the amount of regions in the list changes each week.

Sample1.xlsx
	F
11
Pivots

Sample1.xlsx
	B
18	YOU1591420
Data

The end product should look like this:

 

[BSALV]

what happens next week, as you drill down a second "Montmouth", rename it "Monmouth(2)" or overwrite the 1st ?

 

[welshraz]

what happens next week, as you drill down a second "Montmouth", rename it "Monmouth(2)" or overwrite the 1st ?

Overwrite the first.

 

[BSALV]

are you experienced in VBA ?
This in Contextures and here you find 3 versions Excel Pivot Table DrillDown Show Details

 

[welshraz]

are you experienced in VBA ?
This in Contextures and here you find 3 versions Excel Pivot Table DrillDown Show Details

Thanks for the recommendation. I have managed to pull together what I need.

Just in case anyone is interested. The code I used to create a new tab for each line in the pivot, and then rename is:

Sub TabsForProjects()

Dim pItemCell As Range
Dim pField As PivotField
Dim pT As PivotTable
Dim pItem As PivotItem
Dim rngData As Range

 Set pT = Sheets("Overview").PivotTables("Total")
 Set pField = pT.PivotFields("Total for Tabs")

For Each pItemCell In pField.DataRange
    If pItemCell.Value <> "(blank)" Then
        Set pItem = pField.PivotItems(pItemCell.Value)
        pItem.DataRange.ShowDetail = True
        ActiveSheet.Name = pItem.Name
    End If
Next pItemCell
End Sub