Xirrus1983
New Member
- Joined
- Nov 11, 2008
- Messages
- 37
I have the following Macro that needs to be run on the current active sheet and create the Pivot table on the active sheet as well. Right now I can only get it to run for a specific sheet. How would I change this?
Sub Macro3()
'
' Macro3 Macro
'
'
Range("B28").Select
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Sheet1!R19C1:R1013C16", Version:=xlPivotTableVersion12).CreatePivotTable _
TableDestination:="Sheet1!R1C4", TableName:="PivotTable3", DefaultVersion _
:=xlPivotTableVersion12
Sheets("Sheet1").Select
Cells(1, 4).Select
With ActiveSheet.PivotTables("PivotTable3").PivotFields("Part ID")
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable3").AddDataField ActiveSheet.PivotTables( _
"PivotTable3").PivotFields("Unit " & Chr(10) & "Price"), "Count of Unit " & Chr(10) & "Price", xlCount
ActiveSheet.PivotTables("PivotTable3").AddDataField ActiveSheet.PivotTables( _
"PivotTable3").PivotFields("Unit " & Chr(10) & "Price"), "Count of Unit " & Chr(10) & "Price2", xlCount
ActiveSheet.PivotTables("PivotTable3").AddDataField ActiveSheet.PivotTables( _
"PivotTable3").PivotFields("Unit " & Chr(10) & "Price"), "Count of Unit " & Chr(10) & "Price3", xlCount
With ActiveSheet.PivotTables("PivotTable3").PivotFields("Count of Unit " & Chr(10) & "Price2" _
)
.Caption = "Average of Unit "
.Function = xlAverage
.NumberFormat = "#,##0.00"
End With
With ActiveSheet.PivotTables("PivotTable3").PivotFields("Count of Unit " & Chr(10) & "Price3" _
)
.Caption = "Sum of Unit "
.Function = xlSum
.NumberFormat = "#,##0.00"
End With
Range("E23").Select
End Sub
Sub Macro3()
'
' Macro3 Macro
'
'
Range("B28").Select
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Sheet1!R19C1:R1013C16", Version:=xlPivotTableVersion12).CreatePivotTable _
TableDestination:="Sheet1!R1C4", TableName:="PivotTable3", DefaultVersion _
:=xlPivotTableVersion12
Sheets("Sheet1").Select
Cells(1, 4).Select
With ActiveSheet.PivotTables("PivotTable3").PivotFields("Part ID")
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable3").AddDataField ActiveSheet.PivotTables( _
"PivotTable3").PivotFields("Unit " & Chr(10) & "Price"), "Count of Unit " & Chr(10) & "Price", xlCount
ActiveSheet.PivotTables("PivotTable3").AddDataField ActiveSheet.PivotTables( _
"PivotTable3").PivotFields("Unit " & Chr(10) & "Price"), "Count of Unit " & Chr(10) & "Price2", xlCount
ActiveSheet.PivotTables("PivotTable3").AddDataField ActiveSheet.PivotTables( _
"PivotTable3").PivotFields("Unit " & Chr(10) & "Price"), "Count of Unit " & Chr(10) & "Price3", xlCount
With ActiveSheet.PivotTables("PivotTable3").PivotFields("Count of Unit " & Chr(10) & "Price2" _
)
.Caption = "Average of Unit "
.Function = xlAverage
.NumberFormat = "#,##0.00"
End With
With ActiveSheet.PivotTables("PivotTable3").PivotFields("Count of Unit " & Chr(10) & "Price3" _
)
.Caption = "Sum of Unit "
.Function = xlSum
.NumberFormat = "#,##0.00"
End With
Range("E23").Select
End Sub