Help with VBA Code - Pivot Tables

Yiew33

New Member
Joined
Nov 24, 2015
Messages
43
I currently have the following code to transform data into a pivot table:
VBA Code:
Sub Pivot_Test()

Dim PSheet As Worksheet
Dim DSheet As Worksheet
Dim PCache As PivotCache
Dim PTable As Pivot Table
Dim PRange As Range
Dim LastRow As Long
Dim LastCol As Long

On Error Resume Next
Application.DisplayAlerts = False
Worksheets("Pivot Table").Delete
Sheets.Add After:=ActiveSheet
ActiveSheet.Name = "Pivot Table"
Application.DisplayAlerts = True
Set PSheet = Worksheets("Pivot.Table")
Set DSheet = Worksheets("Conversion Data")

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)

Set PCache = ActiveWorkbook.PivotCaches.Create _
(SourceType:=xlDatabase, SourceData:=PRange). _
CreatePivotTable(TableDestination:=PSheet.Cells(1, 1), _
TableName:="Pivot Table")

Dim range As range
Dim chart As Object
Set chart = ActiveSheet.Shapes.AddChart2

'With ActiveSheet.PivotTables("Pivot Table").PivotFields("Date and Time).AutoGroup
'End With
'range("A2").Select
'Selection.Group Periods:=Array(False, False, False, True, True, False, True)

With ActiveSheet.PivotTables("Pivot Table").PivotFields("Relative Humidity %")
.Orientation = xlDataField
.Position = 1
.Function = xlAverage
.NumberFormat = "#.#0"
.Name = "Reltive Humidity %"
End With

With ActiveSheet.PivotTables("Pivot Table").PivotFields("AM/PM")
.Orientation = xlDataField
.Position = 1
.Function = xlAverage
.NumberFormat = "#.#0"
.Name = "AM/PM"
End With
With ActiveSheet.PivotTables("Pivot Table").PivotFields("Average of AM/PM")
.Orientation = xlColumnField
.Position = 2
End With

ActiveSheet.PivotTables("Pivot Table").ShowTableStyleRowStripes = True

Sheets("Pivot Table").Select
Sheets("Pivot Table").Move After:=Sheet(3)

Sheets("Macro Sheet").Select

End Sub

This is the only way that I could make it so that the AM/PM data moved to the COLUMNS field. I recorded a macro of myself selecting it from the field and placing AM/PM into the columns field, but when I ran the macro it didn't work. The only way I can make it appear in the columns field is if I write it to go into the values field and THEN put the code in to make it go to the columns.

What am I missing so that it just simply goes into the column field?

The recorded macro I tried using is:
VBA Code:
With ActiveSheet.PivotTables("Pivot Table").PivotFields("AM/PM")
.Orientation = xlColumnField
.Position = 1
End With
But this just didn't work.
What I have to do is:
VBA Code:
With ActiveSheet.PivotTables("Pivot Table").PivotFields("AM/PM")
.Orientation = xlDataField
.Position = 1
.Function = xlAverage
.NumberFormat = "#.#0"
.Name = "AM/PM"
End With
With ActiveSheet.PivotTables("Pivot Table").PivotFields("Average of AM/PM")
.Orientation = xlColumnField
.Position = 2
End With
 

Some videos you may like

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

Watch MrExcel Video

Forum statistics

Threads
1,126,895
Messages
5,621,493
Members
415,844
Latest member
Reda Fouad Ramzy

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
Top