CODEWITHSUNDEEP
excelvbapivot-table
Simon Bradbury
Simon Bradbury

Reputation: 1

How do I get my vba pivot table row data into two columns instead of the same column?

I am fairly new to VBA coding in excel and I'm trying to create a pivot table using VBA where I have personnel number (Pers.No.) and employee name (Last name First name) which need to be in two different columns so they show on the same row i.e. I need 1 row of data per person.

I have searched the internet for a fix and tried several different things to fix this but no matter what I do it keeps outputting the name and personnel number in the same column

Sub Pivot2Create()

'Declare Variables
Dim PSheet As Worksheet
Dim DSheet As Worksheet
Dim PCache As PivotCache
Dim PTable As PivotTable
Dim PRange As Range
Dim LastRow As Long
Dim LastCol As Long
Dim wb1 As Workbook


Set wb1 = ThisWorkbook

Application.ScreenUpdating = False
'Insert a New Blank Worksheet
On Error Resume Next
Application.DisplayAlerts = False
Worksheets("Second Pivot").Delete
Sheets.Add Before:=ActiveSheet
ActiveSheet.Name = "Second Pivot"
Application.DisplayAlerts = True
Set PSheet = Worksheets("Second Pivot")
Set DSheet = Worksheets("Current Report")

'Define Data Range
LastRow = DSheet.Cells(Rows.Count, 1).End(xlUp).Row
LastCol = DSheet.Cells(1, Columns.Count).End(xlToLeft).Column
Set PRange = DSheet.Cells(1, 1).Resize(LastRow, LastCol)

'Define Pivot Cache
Set PCache = ActiveWorkbook.PivotCaches.Create _
(SourceType:=xlDatabase, SourceData:=PRange). _
CreatePivotTable(TableDestination:=PSheet.Cells(2, 2), _
TableName:="Uniper3rdParty")

'Insert Blank Pivot Table
Set PTable = PCache.CreatePivotTable _
(TableDestination:=PSheet.Cells(1, 1), TableName:="Uniper3rdParty")

'Insert Row Fields
    With ActiveSheet.PivotTables("Uniper3rdParty").PivotFields("Pers.No.")
    .Orientation = xlRowField
    .RowAxisLayout (xlTabularRow)
    .Position = 1
    End With

    With ActiveSheet.PivotTables("Uniper3rdParty").PivotFields("Last name First name")
    .Orientation = xlRowField
    .RowAxisLayout (xlTabularRow)
    .Position = 2
    End With

'Insert Column Fields
    With ActiveSheet.PivotTables("Uniper3rdParty").PivotFields("Wage Type Long Text")
    .Orientation = xlColumnField
    .Position = 1
    End With

'Insert Data Field
    With ActiveSheet.PivotTables("Uniper3rdParty").PivotFields("Amount")
    .Orientation = xlDataField
    .NumberFormat = "#,##0.00"
    End With

I do not know if I have totally screwed up or if I have gone code blind and I'm just missing something really simple. Any help that anyone can provide would be greatly appreciated

Upvotes: 0

Views: 1305

Answers (2)

simple-solution
simple-solution

Reputation: 1139

Use a "Classic Pivot" (and possible remove subtotals)

Sub FormatPivot()
    With ActiveSheet.PivotTables("Uniper3rdParty")
        .InGridDropZones = True
        .RowAxisLayout xlTabularRow
    End With
    ActiveSheet.PivotTables("Uniper3rdParty").PivotFields("Pers.No.").Subtotals = _
        Array(False, False, False, False, False, False, False, False, False, False, False, False)
End Sub

enter image description here

Upvotes: 0

FXD
FXD

Reputation: 2060

Try the following

With Psheet.PivotTables("Uniper3rdParty")
'What you asked
    .RowAxisLayout xlTabularRow
'Personally, I would add the below rows but feel free to remove them
    dim pf as PivotField
    For Each pf In .PivotFields
        pf.Subtotals = Array(False, False, False, False, False, False, False, False, False, False, False, False)
    Next pf
    .ShowDrillIndicators = False
End With

Upvotes: 1

Related Questions