Adjust Indentation on Pivot Tables

reberryjr

Well-known Member
Joined
Mar 16, 2017
Messages
714
Office Version
  1. 365
Platform
  1. Windows
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.

VBA Code:
'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))

VBA Code:
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
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
Try replacing your For Each/Next loop with the following...

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

Hope this helps!
 
Upvote 0
Try replacing your For Each/Next loop with the following...

VBA Code:
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
VBA Code:
    Range("A33").Select
    Selection.InsertIndent -1
 
Upvote 0
Why do you need to manually tweak the indentation at all? The indentation is usually a sign of a different field.
 
Upvote 0
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.

1689940836186.png
 
Upvote 0
That is not normal behaviour. I'd guess that either the worksheet cell format is odd, or the data actually contains an indentation mark.
 
Upvote 0
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
VBA Code:
    Range("A33").Select
    Selection.InsertIndent -1

Sorry, but I'm a bit confused, since the acceptable values for InsertIndent are 0 to 250, inclusive...
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
Can you put a suitably censored workbook somewhere for us to have a look at?
 
Upvote 0

Forum statistics

Threads
1,223,908
Messages
6,175,305
Members
452,633
Latest member
DougMo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top