andrewstahl
New Member
- Joined
- Mar 4, 2018
- Messages
- 2
I'm working on a very long Macro for my job and I am just about finished, but this is the last part and no matter what I do, I can't seem to figure out the fix. In this PivotTable, I need to have "Managers" as the columns and everything else (i.e. all of the months) in the Values section, with "Values" as the rows. After tinkering around, it seems as though all of the fields registered as CubeFields instead of PivotFields. When I run this, as soon as it gets to .Orientation = xlDataField, it throws a "Run-Time error 5...Invalid procedure call or argument." I also need to make sure that those DataFields are averaged and in a specific number format. Nothing I do works, and any guidance/fixes/workarounds would be greatly appreciated! Below are the macro and a download link to the file for tinkering.
Sub addFieldsToPivot()
Dim pvtTable As PivotTable
Dim cubField As CubeField
Dim i As Long
Dim cubName As String
Set pvtTable = ActiveSheet.PivotTables(1)
For Each cubField In pvtTable.CubeFields
For i = 1 To pvtTable.CubeFields.Count
With pvtTable.CubeFields(i)
If .Name = "[effRent_perBed].[Manager]" Then
.Orientation = xlColumnField
Else:
.Orientation = xlDataField
'has to be averaged
'has to have number format of ##0.00
End If
End With
Next
Next
End Sub
https://drive.google.com/uc?export=download&id=1NLGg8DVEMHnB2Ad7NAKWySevq8naqFjr
Sub addFieldsToPivot()
Dim pvtTable As PivotTable
Dim cubField As CubeField
Dim i As Long
Dim cubName As String
Set pvtTable = ActiveSheet.PivotTables(1)
For Each cubField In pvtTable.CubeFields
For i = 1 To pvtTable.CubeFields.Count
With pvtTable.CubeFields(i)
If .Name = "[effRent_perBed].[Manager]" Then
.Orientation = xlColumnField
Else:
.Orientation = xlDataField
'has to be averaged
'has to have number format of ##0.00
End If
End With
Next
Next
End Sub
https://drive.google.com/uc?export=download&id=1NLGg8DVEMHnB2Ad7NAKWySevq8naqFjr
