Firstly let me state, I'm not macro literate and certainly not an XL expert.
However, I want to record a macro which involves creating a simple pivot table and save it in my personal workbook so it's available at any time for the particular set of data I'm looking at.
The pivot table creation and reording process seems to go fine, displaying the results I want with the resulting Grand Totals at the end of each row.
However when I run the macro on a fresh set of data, the macro obviously works as does the pivot but there is no data appearing in the "Data Items Area", only the grand totals for the rows!
This occurs with both 2007 and 2003 versions and for the life of me I can't figure out how to get the macro to work properly and show the data laid out as I had recorded it. It even happens if I run the macro on the original data I had used to create it!
a copy of the code as follows
ActiveWorkbook.SaveAs Filename:= _
"S:\Training Reports\Div Med Dev training.xlsx", FileFormat:= _
xlOpenXMLWorkbook, CreateBackup:=False
Range("A4").Select
Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"TrainingByDivisionRiskMatron3!R3C1:R5918C8", Version:=xlPivotTableVersion10) _
.CreatePivotTable TableDestination:="Sheet1!R3C1", TableName:="PivotTable1" _
, DefaultVersion:=xlPivotTableVersion10
Sheets("Sheet1").Select
Cells(3, 1).Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Work Area Code")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Status")
.Orientation = xlColumnField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("Status"), "Count of Status", xlCount
End Sub
Any suggestions would be gratefully received.
With thanks
medeng
Bookmarks