Pivot Table from Power Query - reorder Columns (Excel 2021)

[craigcurtin]

Hey Guys,

I have data that is being imported from an accounting package (XERO) it is to do with employee payroll information

AFter a couple of transforms and cleanups of data - the output from Power Query is used to create a Pivot Table.

The pivot is based on Payroll Item types (Overtime, Overtime Saturday, Normal Hours, etc etc)

So we have an employee name and then multiple columns that represent the Payroll item and he will have values in the appropriate columns.

Currently the Column order appears to be Alphabetical, but we require it to be in a set format.

Currently this requires people to manually move the data columns into the order that they wish to see.

I am trying to do this in VBA (it is a set order for the Column names)

I am using the code below - which runs through to completion but does not appear to do anything - this is code that i found on the internet and seems to be correct.

The steps i am using

1) Export data from XERO - which goes directly into an excel sheet as a pseudo table
2) Use Power query with that data as the source
3) Change the date item to remove null values
4) Return the data to a new table on a new sheet

I then have the VBA below

I have confirmed that the sheet name and PT table names are correct.

Sub SortPT()
    Dim rngSort As Variant
    Dim pt As PivotTable
    Dim pf As PivotField
    Dim pi As PivotItem
    Dim lCount As Long
    On Error Resume Next
    Application.EnableEvents = False
    
    rngSort = Array("Payment in lieu of notice", "Annual Leave", "Annual Leave – December 2023", "Personal/Carer's Leave – December 2023", "Bonus", "Other Unpaid Leave", "Casual : Grade 3 SOC/Intel  - Mon-Fri", "Casual : Grade 3 SOC/Intel  - Sat", "Casual : Grade 3 SOC/Intel  - Sun", "Casual : Grade 3 SOC/Intel  - PH", "Casual: Grade 3 FO - Monday - Saturday", "Casual: Grade 3 FO Sunday", "Casual : Grade 3 FO - PH", "Overtime – Special Rate $100/Hour", "Child Support Withheld", "Superannuation Guarantee Contribution (SGC)", "Pre-Tax Voluntary Contribution (RESC)", "PAYG", "Tax on unused leave", "PAYG on Schedule 5", "PAYG on ETP Type O", "STSL Component", "STSL Component on Schedule 5", "Net Pay") 'Adapt to your column headers in sequence
    
    Set pt = Worksheets("Sheet2").PivotTables("PivotTable2") 'Change to correct Worksheet and PivotTable name
    Set pf = pt.PivotFields("Pay Item") 'Change to the correct PivotField name
    lCount = 1
    
    pt.ManualUpdate = True
    With pf
      .AutoSort xlManual, pf.SourceName
      Dim i As Long
      For i = LBound(rngSort) To UBound(rngSort)
        Set pi = Nothing
        Set pi = .PivotItems(rngSort(i))
          If Not pi Is Nothing Then
            If pi.Visible = True Then
              pi.Position = lCount
              lCount = lCount + 1
            End If
          End If
      Next i
    End With
    
    pt.ManualUpdate = False
    pt.RefreshTable
    Application.EnableEvents = True
End Sub

 

[denzo36]

why don't you just use Power Query to do the reordering of columns and not use VBA?

 

[RoryA]

which runs through to completion but does not appear to do anything

You have On Error Resume Next at the start, so you'll never know if the code encounters a problem.

 

[craigcurtin]

why don't you just use Power Query to do the reordering of columns and not use VBA?

Because it does not appear to have any effect on the output Pivot table ? i.e. we have only 4 columns - of distinct data - but when turning it into a pivot table end up with 20 or more columns in the table due to the different pay types

Craig

 

[craigcurtin]

You have On Error Resume Next at the start, so you'll never know if the code encounters a problem.

Aah good one - thanks will comment out and see what happens from there and report back