L
Legacy 352679
Guest
I currently have the following code to transform data into a pivot table:
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:
But this just didn't work.
What I have to do is:
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
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