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, you need to define a rule. For example is it always one of those 3, or maybe it's always where the field name contains the text "Region" or maybe there is something else?

 

[VeeBa]

Hi, you need to define a rule. For example is it always one of those 3, or maybe it's always where the field name contains the text "Region" or maybe there is something else?

Hello! Actually, it's always these 3. Its just that sometimes, the macro I am creating doesn't need to create a file for that specific report (so if its not available, no need to create a pivot for that). But yes, you are correct, it is always these 3

 

[VeeBa]

Below are the cases. If the file/report the macro is doing is for PGP, then the field (for the pivot) is "sub region". If it's home, then it uses "Global Region". Lastly, for Baby it uses "region"

[TABLE="width: 152"]
<tbody>[TR]
[TD="colspan: 2"]File/Report[/TD]
[/TR]
[TR]
[TD]PGP -[/TD]
[TD]Sub Region[/TD]
[/TR]
[TR]
[TD]Home[/TD]
[TD]- Global Region[/TD]
[/TR]
[TR]
[TD]Baby -[/TD]
[TD]Region[/TD]
[/TR]
</tbody><colgroup><col><col></colgroup>[/TABLE]

 

[FormR]

it is always these 3

Then how about simply:

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" Or pf.Name = "Global Region" Or pf.Name = "Region" Then
        For Each pi In pf.PivotItems
            pi.ShowDetail = False
        Next pi
    End If
Next pf


End Sub

 

[VeeBa]

Sorry for the late response, above code works! Thanks a lot FormR!