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