Pivot table missing columns...
Hello all,
I have a macro that runs and creates a pivot table from some source data that is different each week. I have pivot columns like "Q4 2004", "Q1 2005", and "Q2 2005". I have programmatically told the pivot table to place the "Q1 2004" column in the first position, etc. That works fine as long as there is data corresponding to that quarter. Some of the data it is created from contains info from that quarter and some does not. If there is no data from quarter 4 2004 then the macro errors out, telling me that it could not find any column called "Q4 2004". I am wondering if I can do something like "If there is a "Q4 2004", put it first, else ignore it." Anyone have any suggestions? Here is a snippet of code asd it stands now: Dim i As Integer ActiveCell.SpecialCells(xlLastCell).Select i = Selection.Row ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _ "Credits!R1C1:R" & i & "C14").CreatePivotTable TableDestination:="", TableName:= _ "PivotTable1", DefaultVersion:=xlPivotTableVersion10 ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1) ActiveSheet.Cells(3, 1).Select With ActiveSheet.PivotTables("PivotTable1").PivotFields ("COLLECTOR") .Orientation = xlRowField .Position = 1 End With With ActiveSheet.PivotTables("PivotTable1").PivotFields ("QUARTER") .Orientation = xlColumnField .Position = 1 End With ActiveSheet.PivotTables("PivotTable1").AddDataFiel d ActiveSheet.PivotTables( _ "PivotTable1").PivotFields("CURRENT"), "Sum of CURRENT", xlSum ActiveSheet.PivotTables("PivotTable1").PivotFields ("QUARTER").PivotItems("Q2 2005") _ .Position = 5 ActiveSheet.PivotTables("PivotTable1").PivotFields ("QUARTER").PivotItems("Q1 2005") _ .Position = 4 ActiveSheet.PivotTables("PivotTable1").PivotFields ("QUARTER").PivotItems("Q4 2004") _ .Position = 3 ActiveSheet.PivotTables("PivotTable1").PivotFields ("QUARTER").PivotItems("Q3 2004") _ .Position = 2 ActiveSheet.PivotTables("PivotTable1").PivotFields ("QUARTER").PivotItems("Q2 2004") _ .Position = 1 Thanks, in advance for any assistance. Trevor |
All times are GMT +1. The time now is 10:32 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com