CODEWITHSUNDEEP
excelvbapivot-table
Peter Harrison
Peter Harrison

Reputation: 1

VBA Code adds pivot table field, but can't adjust position?

I'm writing a code to show a pivot field and I need the new field in a specific position. If I delete the ".Position = 1" line it works, but if I leave it in I get an error that says "Run-time error '424': Object required".

I'd really appreciate any help in learning how to fix or get around this.

Sub projid()

Range("C4").Select
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
    "PivotTable1").PivotFields("Projects_idProjects"), "Sum of Projects_idProjects" _
    , xlSum
With ActiveSheet.PivotTables("PivotTable1").PivotFields( _
    "Sum of Projects_idProjects")
    .Orientation = xlRowField
End With
Range("g4").Value = 1

End If
End Sub

Upvotes: 0

Views: 573

Answers (1)

Michal
Michal

Reputation: 5998

This should help you with your quest:

'Define Pivot Cache
Set pvtCache = ActiveWorkbook.PivotCaches.Create _
    (SourceType:=xlDatabase, SourceData:=Range(SOURCE_DATA_RANGE)). _
    CreatePivotTable(TableDestination:=Range(DESTINATION_DATA_RANGE), _
    TableName:="TABLE_NAME_GOES_HERE")


with ActiveSheet.PivotTables ("TABLE_NAME_GOES_HERE")

    ' Insert Row Fields
    With .PivotFields("FIELD_NAME")
        .Orientation = xlRowField
        .Position = 1
    End With

    With .PivotFields("ANOTHER_FIELD_NAME")
        .Orientation = xlRowField
        .Position = 2
    End With

    'Insert Data Field
    With .PivotFields("DATA_FIELD_GOES_HERE")
        .Orientation = xlDataField
        .Position = 1
    End With

End With`

Upvotes: 1

Related Questions