Shorten code

[VeeBa]

Hi All - I am trying to record a code wherein I am inserting a blank ine after each item in my pivot table. I was able to generate below code, question is, is there a way to shorten this code?

Sub Macro1()
'
' Macro1 Macro
'
'
    ActiveSheet.PivotTables("PivotTable1").PivotSelect "'Function Name'[All]", _
        xlLabelOnly + xlFirstRow, True
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Cost Center"). _
        LayoutBlankLine = True
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Cost Element"). _
        LayoutBlankLine = True
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Cost element name"). _
        LayoutBlankLine = True
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Ttl Fx+Vbl value CAC"). _
        LayoutBlankLine = True
    ActiveSheet.PivotTables("PivotTable1").PivotFields("CO area currency"). _
        LayoutBlankLine = True
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Ttl Fxd/Vbl value OC"). _
        LayoutBlankLine = True
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Object Currency"). _
        LayoutBlankLine = True
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Created on"). _
        LayoutBlankLine = True
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Entered by"). _
        LayoutBlankLine = True
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Total val. rep.crcy"). _
        LayoutBlankLine = True
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Report currency"). _
        LayoutBlankLine = True
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Document Header Text"). _
        LayoutBlankLine = True
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Document Number"). _
        LayoutBlankLine = True
    ActiveSheet.PivotTables("PivotTable1").PivotFields("$ MM").LayoutBlankLine = _
        True
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Function Name"). _
        LayoutBlankLine = True
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Report Function"). _
        LayoutBlankLine = True
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Budget Upload Contact"). _
        LayoutBlankLine = True
    ActiveSheet.PivotTables("PivotTable1").PivotFields( _
        "Blocked/Unblocked/To be blocked").LayoutBlankLine = True
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Business").LayoutBlankLine _
        = True
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Sub Region"). _
        LayoutBlankLine = True
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Major Org"). _
        LayoutBlankLine = True
    ActiveSheet.PivotTables("PivotTable1").PivotFields("B Org").LayoutBlankLine = _
        True
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Global Region"). _
        LayoutBlankLine = True
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Organization"). _
        LayoutBlankLine = True
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Market/Function"). _
        LayoutBlankLine = True
    ActiveSheet.PivotTables("PivotTable1").PivotFields("GBU").LayoutBlankLine = _
        True
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Sub-function"). _
        LayoutBlankLine = True
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Region").LayoutBlankLine = _
        True
    Range("B6").Select
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Sub Region").PivotItems( _
        "APAC PGP").ShowDetail = False
    Range("B8").Select
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Sub Region").PivotItems( _
        "EUROPE PGP").ShowDetail = False
    Range("B10").Select
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Sub Region").PivotItems( _
        "GLBL PGP").ShowDetail = False
    Range("B12").Select
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Sub Region").PivotItems( _
        "NA PGP").ShowDetail = False
End Sub

 

[FormR]

Hi, this isn't tested but might give you some ideas:

Sub Macro1()
Dim pt As PivotTable, pf As PivotField


Set pt = ActiveSheet.PivotTables("PivotTable1")


pt.PivotSelect "'Function Name'[All]", xlLabelOnly + xlFirstRow, True


For Each pf In pt.PageFields
    pf.LayoutBlankLine = True
Next pf


With pt.PivotFields("Sub Region")
    .PivotItems("APAC PGP").ShowDetail = False
    .PivotItems("EUROPE PGP").ShowDetail = False
    .PivotItems("GLBL PGP").ShowDetail = False
    .PivotItems("NA PGP").ShowDetail = False
End With


End Sub

 

[VeeBa]

Hi, this isn't tested but might give you some ideas:

Sub Macro1()
Dim pt As PivotTable, pf As PivotField


Set pt = ActiveSheet.PivotTables("PivotTable1")


pt.PivotSelect "'Function Name'[All]", xlLabelOnly + xlFirstRow, True


For Each pf In pt.PageFields
    pf.LayoutBlankLine = True
Next pf


With pt.PivotFields("Sub Region")
    .PivotItems("APAC PGP").ShowDetail = False
    .PivotItems("EUROPE PGP").ShowDetail = False
    .PivotItems("GLBL PGP").ShowDetail = False
    .PivotItems("NA PGP").ShowDetail = False
End With


End Sub

Thanks! But what if the "sub region" fields are not fixed? For example sometimes I have apac pgp, sometimes not. Is there a way to not hard-code it to these? Or maybe skip if not present? Thanks!

 

[FormR]

Is there a way to not hard-code it to these? Or maybe skip if not present?

Hi, I'm sure there is, but what is the logic for deciding which ones are included and which ones are skipped?

 

[VeeBa]

Hi, I'm sure there is, but what is the logic for deciding which ones are included and which ones are skipped?

I mean there will be instances when the pivotfield "Sub region" could show different details (meaning, its not a fixed "APAC PGP", "EUROPE PGP", "GLBL PGP", "NA PGP"). Sometimes there will be just 1 or 2 details, sometimes more than 5. And the values are not fixed - this is the reason why I am trying not to hard code below part..The only fixed part here is that the pivotfield "Sub Region". Hope my explanation helps. Thank you!

pt.PivotFields("Sub Region")
    .PivotItems("APAC PGP").ShowDetail = False
    .PivotItems("EUROPE PGP").ShowDetail = False
    .PivotItems("GLBL PGP").ShowDetail = False
    .PivotItems("NA PGP").ShowDetail = False
End With

 

[VeeBa]

basically the goal I am trying to achieve is to hide the detail for all "sub region" items. Because sometimes there could be items that are unknown/new, that's why I wanted to not hard code this part to avoid any errors or to missed anything. I only came up with initial code since this is only an output of the recording I made.. Most of the time it will have more pivot items to show (not just 4).

 

[FormR]

hide the detail for all "sub region" items.

Hi, that's the golden nugget

Again, not really tested, but you can try:

Sub Macro1()
Dim pt As PivotTable, pf As PivotField, pi As PivotItem


Set pt = ActiveSheet.PivotTables("PivotTable1")


pt.PivotSelect "'Function Name'[All]", xlLabelOnly + xlFirstRow, True


For Each pf In pt.PivotFields
    pf.LayoutBlankLine = True
    If pf.Name = "Sub Region" Then
        For Each pi In pf.PivotItems
            pi.ShowDetail = False
        Next pi
    End If
Next pf


End Sub

 

[VeeBa]

Worked! Just a curious question, if the "Sub Region" part is not fixed? Cause sometimes its just "region" instead of "sub region"

 

[FormR]

Hi, again - you would need define what the logic is for determining which pivot field the code needs to apply to.

 

[VeeBa]

Hello! Some of my pivots have "Global Region" some have "Region" and lastly, which you've helped me with already - "Sub Region"

How do I hide the items for these fields? Can I maybe do If, Else if?