Pivot Tables using VBA
 

Greetings,

Ive recorded the macro below and would like to make modifications to it so
it can use variables. Hence, making it useful on identical data other than
the two workbooks here.

I have three workbooks open in an Excel session. The data is identical (in
type) between two books, and I place the pivot table into the third book. The
problems to solve are the file name changes based on the date the data was
extracted, and the number of rows will change as well.

e.g. 'K:\Work\My Documents\Excel Files\[10-Aug-07 S2K
6N4M.xls]part'!R1C1:R66C10").

The other problem with using this macro on a new set of files is parsing the
€œENGINE STATUS€� field in both tables and hiding select values. I would need
to have the code check through a €˜list of PivotItems and if they exist, hide
them. These values to hide would be identical for both tables.

I hope I have been clear enough in my verbiage that a solution can be found.

Thanks.




Option Explicit

Sub MyPivotTableRound2()
'
' MyPivotTableRound2 Macro
' Macro recorded 8/22/2007 by Hal Innes
'

'
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
"'K:\Work\My Documents\Excel Files\[10-Aug-07 S2K
6N4M.xls]part'!R1C1:R66C10"). _
CreatePivotTable TableDestination:="[Book1]Sheet1!R3C1", TableName:= _
"PivotTable1", DefaultVersion:=xlPivotTableVersion10
Range("A3").Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields ("CONTROLLER")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields ("GROUP NO")
.Orientation = xlRowField
.Position = 2
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields ("ENGINE STATUS")
.Orientation = xlRowField
.Position = 3
End With
ActiveSheet.PivotTables("PivotTable1").AddDataFiel d
ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("MODEL NO"), "Count of MODEL NO", xlCount
Range("F3").Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
"'K:\Work\My Documents\Excel Files\[10-Aug-07 S4K
6N4M.xls]part'!R1C1:R61C10"). _
CreatePivotTable TableDestination:="[Book1]Sheet1!R3C6", TableName:= _
"PivotTable2", DefaultVersion:=xlPivotTableVersion10
Range("F3").Select
With ActiveSheet.PivotTables("PivotTable2").PivotFields ("CONTROLLER")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable2").PivotFields ("GROUP NO")
.Orientation = xlRowField
.Position = 2
End With
With ActiveSheet.PivotTables("PivotTable2").PivotFields ("ENGINE STATUS")
.Orientation = xlRowField
.Position = 3
End With
ActiveSheet.PivotTables("PivotTable2").AddDataFiel d
ActiveSheet.PivotTables( _
"PivotTable2").PivotFields("MODEL NO"), "Count of MODEL NO", xlCount
ActiveWorkbook.ShowPivotTableFieldList = False
Application.CommandBars("PivotTable").Visible = False
With ActiveSheet.PivotTables("PivotTable1").PivotFields ("ENGINE STATUS")
.PivotItems("IN-SP").Visible = False
.PivotItems("SD-RV").Visible = False
.PivotItems("SD-WF").Visible = False
.PivotItems("TS367-093").Visible = False
.PivotItems("TS367-094").Visible = False
.PivotItems("TS367-096").Visible = False
.PivotItems("TS-FA").Visible = False
.PivotItems("TS-SA").Visible = False
End With
With ActiveSheet.PivotTables("PivotTable2").PivotFields ("ENGINE STATUS")
.PivotItems("LD-RV").Visible = False
.PivotItems("SD-WF").Visible = False
.PivotItems("TS367-092").Visible = False
.PivotItems("TS367-095").Visible = False
.PivotItems("TS-EN").Visible = False
.PivotItems("TS-FA").Visible = False
End With
Range("A1").Select
End Sub