Adjust Indentation on Pivot Tables

[reberryjr]

Pivot tables are frustrating me again. I use the underlying code to refresh a pivot table, but if a new item has been added to the table, that item is indented twice as far as the others. In a "normal" cell it would be an indentation of 2, but I need it to be 1. I can't seem to find anything on the web that says how to adjust indentation in a pivot table. I took a stab at it, and have included that below as well.

'This is what I'm using to refrsh the pivot table.
'mS.PivotTables("InitSummary").ChangePivotCache ActiveWorkbook.PivotCaches.Create(xlDatabase, SourceData:=m.Sheets("Inventory").Range("A2:AE" & mILR))

Sub Macro1()

Dim m As Workbook
Dim mS As Worksheet
Dim PT As PivotTable
Dim PF As PivotField
Dim c As Range

Set m = ThisWorkbook
Set mS = m.Sheets("Summaries")

Set PT = mS.PivotTables("InitSummary")

For Each c In PT.RowFields("Title")  ''I'm getting the "Object doesn't support this property or method" error.
    If c.IndentLevel = 2 Then
        c.IndentLevel = 1
    Else: Exit Sub
    End If
Next c

'This is what I'm using to refrsh the pivot table.
'mS.PivotTables("InitSummary").ChangePivotCache ActiveWorkbook.PivotCaches.Create(xlDatabase, SourceData:=m.Sheets("Inventory").Range("A2:AE" & mILR))

End Sub

 

[Domenic]

Try replacing your For Each/Next loop with the following...

With PT.RowFields("Title").DataRange
    If .IndentLevel = 2 Then
        .IndentLevel = 1
    End If
End With

Hope this helps!

 

[reberryjr]

Try replacing your For Each/Next loop with the following...

With PT.RowFields("Title").DataRange
    If .IndentLevel = 2 Then
        .IndentLevel = 1
    End If
End With

Hope this helps!

Thanks @Domenic . I utilized your suggestion and stopped getting an error, but the indentation isn't reduced. If it's any help, the macro recorder gave me this

    Range("A33").Select
    Selection.InsertIndent -1

 

[RoryA]

Why do you need to manually tweak the indentation at all? The indentation is usually a sign of a different field.

 

[reberryjr]

Why do you need to manually tweak the indentation at all? The indentation is usually a sign of a different field.

@RoryA See the markup below...the pivot table is reading the same exact column, but for some reason when the pivot table gets refreshed, new work items are being indented too far.

 

[RoryA]

That is not normal behaviour. I'd guess that either the worksheet cell format is odd, or the data actually contains an indentation mark.

 

[Domenic]

Thanks @Domenic . I utilized your suggestion and stopped getting an error, but the indentation isn't reduced. If it's any help, the macro recorder gave me this

    Range("A33").Select
    Selection.InsertIndent -1

Sorry, but I'm a bit confused, since the acceptable values for InsertIndent are 0 to 250, inclusive...

 

[reberryjr]

Sorry, but I'm a bit confused, since the acceptable values for InsertIndent are 0 to 250, inclusive...

@Domenic I'm not sure what to say. I turned the recorder on, manually reduced the indentation, turned the recorder off, and that's what the recorder spit out.

 

[reberryjr]

That is not normal behaviour. I'd guess that either the worksheet cell format is odd, or the data actually contains an indentation mark.

@RoryA I checked, and the cell is formatted as General with no preceding zeroes.

 

[RoryA]

Can you put a suitably censored workbook somewhere for us to have a look at?