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

 

[reberryjr]

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

Unfortunately, that's not an option on my work laptop. I might just have to write code to trash the current pivot table, and create a new one when someone tries to refresh it.

 

[Domenic]

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

After taking another look, I see that you were actually talking about InsertIndent, not IndentLevel as per original post. The former is a method, whereas the latter is a property.

Maybe the reason that your original code does not change the indent level for the cell is that the existing indent level is actually greater than 2, and so If .IndentLevel = 2 Then evaluates to False. Does this help?

    If c.IndentLevel <> 1 Then
        c.IndentLevel = 1
    Else: Exit Sub
    End If

 

[reberryjr]

After taking another look, I see that you were actually talking about InsertIndent, not IndentLevel as per original post. The former is a method, whereas the latter is a property.

Maybe the reason that your original code does not change the indent level for the cell is that the existing indent level is actually greater than 2, and so If .IndentLevel = 2 Then evaluates to False. Does this help?

    If c.IndentLevel <> 1 Then
        c.IndentLevel = 1
    Else: Exit Sub
    End If

@Domenic I'm trying to see if your suggestion will work, but I'm getting the Object doesn't support this property or method error on the For line

For Each c In mS.PivotTables("InitSummary").RowFields("Title")
    If c.IndentLevel <> 1 Then
        c.IndentLevel = 1
    Else: Exit Sub
    End If
Next c

 

[Domenic]

Actually, that should be . . .

For Each c In mS.PivotTables("InitSummary").RowFields("Title").DataRange