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

craigcurtin

New Member
Joined
Aug 11, 2023
Messages
3
Office Version
  1. 2021
Platform
  1. Windows
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.

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

Attachments

  • Pivottable2.jpg
    Pivottable2.jpg
    84.3 KB · Views: 15

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
why don't you just use Power Query to do the reordering of columns and not use VBA?
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,216,500
Messages
6,131,016
Members
449,615
Latest member
Nic0la

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