|
Ho to Delete "Ghost" Pivot Tables
Hi,
I use Office 2000. I have one spreadsheet that has several sheets with pivot tables. One sheet has multiple pivot tables. Today, I suddenly have extra, unwanted, blank (hence ghost) pivot tables inserted in 2 of my sheets. I've been using these sheets for about a year with no previous problems. I have no idea how these "ghost" pivot tables suddenly showed up in my sheets. I can't find a way to delete them. Question : How do I get rid of them ? I do have some VB code that spools through the sheets/tables to do a "refresh". It is not very effeceint, but it seems to work fine. ' ' Refresh The Master_Pivot_DATA Table ' Sheets("P&L").Activate ActiveSheet.PivotTables("MASTER_PIVOT_DATA").Pivot Select "'Job Number'[All]", _ xlLabelOnly ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _ "'Job Costing Master Data'!R2C1:R" & LastRowOfJobCostingMasterData & "C35" ActiveSheet.PivotTables("MASTER_PIVOT_DATA").Small Grid = False ActiveSheet.PivotTables("MASTER_PIVOT_DATA").AddFi elds RowFields:= _ "Job Number", ColumnFields:="Data", PageFields:=Array("Calander Year", _ "Fiscal Year", "Month", "Superintendant", "Wall Crew", "FTG Crew") Range("D1").Select ' ActiveSheet.PivotTables("MASTER_PIVOT_DATA").Refre shTable ' Application.StatusBar = "<<== BE PATIENT == The MASTER_PIVOT_DATA Table Has Been Refreshed" ' ' Refresh PivotTable1 ' ActiveSheet.PivotTables("PivotTable1").PivotSelect "Superintendant[All]", _ xlLabelOnly ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _ "'Job Costing Master Data'!R2C1:R" & LastRowOfJobCostingMasterData & "C35" ActiveSheet.PivotTables("PivotTable1").SmallGrid = False ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:="Superintendant" _ , ColumnFields:="Data", PageFields:=Array("Calander Year", "Fiscal Year", _ "Month") ' ActiveSheet.PivotTables("PivotTable1").RefreshTabl e ' Application.StatusBar = "<<== BE PATIENT == PivotTable 1 Has Been Refreshed" ' ' Refresh PivotTable2 ' ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _ "'Job Costing Master Data'!R2C1:R" & LastRowOfJobCostingMasterData & "C35" ActiveSheet.PivotTables("PivotTable2").SmallGrid = False ActiveSheet.PivotTables("PivotTable2").AddFields RowFields:="Wall Crew", _ ColumnFields:="Data", PageFields:=Array("Calander Year", "Fiscal Year", _ "Month") ActiveSheet.PivotTables("PivotTable3").PivotSelect "'FTG Crew'[All]", _ xlLabelOnly ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _ "'Job Costing Master Data'!R2C1:R1323C35" ActiveSheet.PivotTables("PivotTable3").SmallGrid = False ActiveSheet.PivotTables("PivotTable3").AddFields RowFields:="FTG Crew", _ ColumnFields:="Data", PageFields:=Array("Calander Year", "Fiscal Year", _ "Month") ' ActiveSheet.PivotTables("PivotTable2").RefreshTabl e ' Application.StatusBar = "<<== BE PATIENT == PivotTable 2 Has Been Refreshed" ' ' Refresh PivotTable3 ' ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _ "'Job Costing Master Data'!R2C1:R" & LastRowOfJobCostingMasterData & "C35" ActiveSheet.PivotTables("PivotTable3").SmallGrid = False ActiveSheet.PivotTables("PivotTable3").AddFields RowFields:="FTG Crew", _ ColumnFields:="Data", PageFields:=Array("Calander Year", "Fiscal Year", _ "Month") ' ActiveSheet.PivotTables("PivotTable3").RefreshTabl e ' Application.StatusBar = "<<== BE PATIENT == PivotTable 3 Has Been Refreshed" ' ' Refresh PivotTable4 ' ActiveSheet.PivotTables("PivotTable4").PivotSelect "Builder[All]", xlLabelOnly ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _ "'Job Costing Master Data'!R2C1:R" & LastRowOfJobCostingMasterData & "C35" ActiveSheet.PivotTables("PivotTable4").SmallGrid = False ActiveSheet.PivotTables("PivotTable4").AddFields RowFields:="Builder", _ ColumnFields:="Data", PageFields:=Array("Calander Year", "Fiscal Year", _ "Month") ' ActiveSheet.PivotTables("PivotTable4").RefreshTabl e ' Application.StatusBar = "<<== BE PATIENT == PivotTable 4 Has Been Refreshed" ' ' Refresh PivotTable4 on Superintendant Sheet ' Sheets("Superintendant").Select ActiveSheet.PivotTables("PivotTable4").PivotSelect "Superintendant[All]", _ xlLabelOnly ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _ "'Job Costing Master Data'!R2C1:R" & LastRowOfJobCostingMasterData & "C35" ActiveSheet.PivotTables("PivotTable4").SmallGrid = False ActiveSheet.PivotTables("PivotTable4").AddFields RowFields:="Superintendant" _ , ColumnFields:="Data", PageFields:=Array("Calander Year", "Fiscal Year", _ "Month") ' ActiveSheet.PivotTables("PivotTable4").RefreshTabl e ' Application.StatusBar = "<<== BE PATIENT == PivotTable 4 On Superintendant Sheet Has Been Refreshed" ' ' ' Refresh PivotTable4 on Builder Sheet ' Sheets("Builder").Select ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _ "'Job Costing Master Data'!R2C1:R" & LastRowOfJobCostingMasterData & "C35" ActiveSheet.PivotTables("PivotTable4").SmallGrid = False ActiveSheet.PivotTables("PivotTable4").AddFields RowFields:="Builder", _ ColumnFields:="Data", PageFields:=Array("Calander Year", "Fiscal Year", _ "Month") ' ActiveSheet.PivotTables("PivotTable4").RefreshTabl e ' Application.StatusBar = "<<== BE PATIENT == PivotTable 4 On Builder Sheet Has Been Refreshed" ' ' Refresh PivotTable1 on FreeForm Analysis Sheet ' Sheets("FreeForm Analysis Table").Select ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _ "'Job Costing Master Data'!R2C1:R" & LastRowOfJobCostingMasterData & "C35" ActiveSheet.PivotTables("PivotTable1").SmallGrid = False ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:="Wall Crew", _ ColumnFields:="Data", PageFields:=Array("Builder", "Calander Year", _ "Fiscal Year", "Month") I would have preferred the simpler .RefreshTable solution for each pivot table, but I was getting spurrious results with following code. ActiveSheet.PivotTables("MASTER_PIVOT_DATA").Refre shTable Is my bad vb code suddenly causing this problem ? thanks in advance, tim |
Ho to Delete "Ghost" Pivot Tables
highlight the area surronuding the pivot table and do edit, clear, all
"needyourhelp" wrote: Hi, I use Office 2000. I have one spreadsheet that has several sheets with pivot tables. One sheet has multiple pivot tables. Today, I suddenly have extra, unwanted, blank (hence ghost) pivot tables inserted in 2 of my sheets. I've been using these sheets for about a year with no previous problems. I have no idea how these "ghost" pivot tables suddenly showed up in my sheets. I can't find a way to delete them. Question : How do I get rid of them ? I do have some VB code that spools through the sheets/tables to do a "refresh". It is not very effeceint, but it seems to work fine. ' ' Refresh The Master_Pivot_DATA Table ' Sheets("P&L").Activate ActiveSheet.PivotTables("MASTER_PIVOT_DATA").Pivot Select "'Job Number'[All]", _ xlLabelOnly ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _ "'Job Costing Master Data'!R2C1:R" & LastRowOfJobCostingMasterData & "C35" ActiveSheet.PivotTables("MASTER_PIVOT_DATA").Small Grid = False ActiveSheet.PivotTables("MASTER_PIVOT_DATA").AddFi elds RowFields:= _ "Job Number", ColumnFields:="Data", PageFields:=Array("Calander Year", _ "Fiscal Year", "Month", "Superintendant", "Wall Crew", "FTG Crew") Range("D1").Select ' ActiveSheet.PivotTables("MASTER_PIVOT_DATA").Refre shTable ' Application.StatusBar = "<<== BE PATIENT == The MASTER_PIVOT_DATA Table Has Been Refreshed" ' ' Refresh PivotTable1 ' ActiveSheet.PivotTables("PivotTable1").PivotSelect "Superintendant[All]", _ xlLabelOnly ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _ "'Job Costing Master Data'!R2C1:R" & LastRowOfJobCostingMasterData & "C35" ActiveSheet.PivotTables("PivotTable1").SmallGrid = False ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:="Superintendant" _ , ColumnFields:="Data", PageFields:=Array("Calander Year", "Fiscal Year", _ "Month") ' ActiveSheet.PivotTables("PivotTable1").RefreshTabl e ' Application.StatusBar = "<<== BE PATIENT == PivotTable 1 Has Been Refreshed" ' ' Refresh PivotTable2 ' ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _ "'Job Costing Master Data'!R2C1:R" & LastRowOfJobCostingMasterData & "C35" ActiveSheet.PivotTables("PivotTable2").SmallGrid = False ActiveSheet.PivotTables("PivotTable2").AddFields RowFields:="Wall Crew", _ ColumnFields:="Data", PageFields:=Array("Calander Year", "Fiscal Year", _ "Month") ActiveSheet.PivotTables("PivotTable3").PivotSelect "'FTG Crew'[All]", _ xlLabelOnly ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _ "'Job Costing Master Data'!R2C1:R1323C35" ActiveSheet.PivotTables("PivotTable3").SmallGrid = False ActiveSheet.PivotTables("PivotTable3").AddFields RowFields:="FTG Crew", _ ColumnFields:="Data", PageFields:=Array("Calander Year", "Fiscal Year", _ "Month") ' ActiveSheet.PivotTables("PivotTable2").RefreshTabl e ' Application.StatusBar = "<<== BE PATIENT == PivotTable 2 Has Been Refreshed" ' ' Refresh PivotTable3 ' ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _ "'Job Costing Master Data'!R2C1:R" & LastRowOfJobCostingMasterData & "C35" ActiveSheet.PivotTables("PivotTable3").SmallGrid = False ActiveSheet.PivotTables("PivotTable3").AddFields RowFields:="FTG Crew", _ ColumnFields:="Data", PageFields:=Array("Calander Year", "Fiscal Year", _ "Month") ' ActiveSheet.PivotTables("PivotTable3").RefreshTabl e ' Application.StatusBar = "<<== BE PATIENT == PivotTable 3 Has Been Refreshed" ' ' Refresh PivotTable4 ' ActiveSheet.PivotTables("PivotTable4").PivotSelect "Builder[All]", xlLabelOnly ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _ "'Job Costing Master Data'!R2C1:R" & LastRowOfJobCostingMasterData & "C35" ActiveSheet.PivotTables("PivotTable4").SmallGrid = False ActiveSheet.PivotTables("PivotTable4").AddFields RowFields:="Builder", _ ColumnFields:="Data", PageFields:=Array("Calander Year", "Fiscal Year", _ "Month") ' ActiveSheet.PivotTables("PivotTable4").RefreshTabl e ' Application.StatusBar = "<<== BE PATIENT == PivotTable 4 Has Been Refreshed" ' ' Refresh PivotTable4 on Superintendant Sheet ' Sheets("Superintendant").Select ActiveSheet.PivotTables("PivotTable4").PivotSelect "Superintendant[All]", _ xlLabelOnly ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _ "'Job Costing Master Data'!R2C1:R" & LastRowOfJobCostingMasterData & "C35" ActiveSheet.PivotTables("PivotTable4").SmallGrid = False ActiveSheet.PivotTables("PivotTable4").AddFields RowFields:="Superintendant" _ , ColumnFields:="Data", PageFields:=Array("Calander Year", "Fiscal Year", _ "Month") ' ActiveSheet.PivotTables("PivotTable4").RefreshTabl e ' Application.StatusBar = "<<== BE PATIENT == PivotTable 4 On Superintendant Sheet Has Been Refreshed" ' ' ' Refresh PivotTable4 on Builder Sheet ' Sheets("Builder").Select ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _ "'Job Costing Master Data'!R2C1:R" & LastRowOfJobCostingMasterData & "C35" ActiveSheet.PivotTables("PivotTable4").SmallGrid = False ActiveSheet.PivotTables("PivotTable4").AddFields RowFields:="Builder", _ ColumnFields:="Data", PageFields:=Array("Calander Year", "Fiscal Year", _ "Month") ' ActiveSheet.PivotTables("PivotTable4").RefreshTabl e ' Application.StatusBar = "<<== BE PATIENT == PivotTable 4 On Builder Sheet Has Been Refreshed" ' ' Refresh PivotTable1 on FreeForm Analysis Sheet ' Sheets("FreeForm Analysis Table").Select ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _ "'Job Costing Master Data'!R2C1:R" & LastRowOfJobCostingMasterData & "C35" ActiveSheet.PivotTables("PivotTable1").SmallGrid = False ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:="Wall Crew", _ ColumnFields:="Data", PageFields:=Array("Builder", "Calander Year", _ "Fiscal Year", "Month") I would have preferred the simpler .RefreshTable solution for each pivot table, but I was getting spurrious results with following code. ActiveSheet.PivotTables("MASTER_PIVOT_DATA").Refre shTable Is my bad vb code suddenly causing this problem ? thanks in advance, tim |
Ho to Delete "Ghost" Pivot Tables
Thanks Dave.
I guess I don't think sometimes. Any idea where they came from ? tim "Dave Breitenbach" wrote: highlight the area surronuding the pivot table and do edit, clear, all "needyourhelp" wrote: Hi, I use Office 2000. I have one spreadsheet that has several sheets with pivot tables. One sheet has multiple pivot tables. Today, I suddenly have extra, unwanted, blank (hence ghost) pivot tables inserted in 2 of my sheets. I've been using these sheets for about a year with no previous problems. I have no idea how these "ghost" pivot tables suddenly showed up in my sheets. I can't find a way to delete them. Question : How do I get rid of them ? I do have some VB code that spools through the sheets/tables to do a "refresh". It is not very effeceint, but it seems to work fine. ' ' Refresh The Master_Pivot_DATA Table ' Sheets("P&L").Activate ActiveSheet.PivotTables("MASTER_PIVOT_DATA").Pivot Select "'Job Number'[All]", _ xlLabelOnly ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _ "'Job Costing Master Data'!R2C1:R" & LastRowOfJobCostingMasterData & "C35" ActiveSheet.PivotTables("MASTER_PIVOT_DATA").Small Grid = False ActiveSheet.PivotTables("MASTER_PIVOT_DATA").AddFi elds RowFields:= _ "Job Number", ColumnFields:="Data", PageFields:=Array("Calander Year", _ "Fiscal Year", "Month", "Superintendant", "Wall Crew", "FTG Crew") Range("D1").Select ' ActiveSheet.PivotTables("MASTER_PIVOT_DATA").Refre shTable ' Application.StatusBar = "<<== BE PATIENT == The MASTER_PIVOT_DATA Table Has Been Refreshed" ' ' Refresh PivotTable1 ' ActiveSheet.PivotTables("PivotTable1").PivotSelect "Superintendant[All]", _ xlLabelOnly ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _ "'Job Costing Master Data'!R2C1:R" & LastRowOfJobCostingMasterData & "C35" ActiveSheet.PivotTables("PivotTable1").SmallGrid = False ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:="Superintendant" _ , ColumnFields:="Data", PageFields:=Array("Calander Year", "Fiscal Year", _ "Month") ' ActiveSheet.PivotTables("PivotTable1").RefreshTabl e ' Application.StatusBar = "<<== BE PATIENT == PivotTable 1 Has Been Refreshed" ' ' Refresh PivotTable2 ' ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _ "'Job Costing Master Data'!R2C1:R" & LastRowOfJobCostingMasterData & "C35" ActiveSheet.PivotTables("PivotTable2").SmallGrid = False ActiveSheet.PivotTables("PivotTable2").AddFields RowFields:="Wall Crew", _ ColumnFields:="Data", PageFields:=Array("Calander Year", "Fiscal Year", _ "Month") ActiveSheet.PivotTables("PivotTable3").PivotSelect "'FTG Crew'[All]", _ xlLabelOnly ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _ "'Job Costing Master Data'!R2C1:R1323C35" ActiveSheet.PivotTables("PivotTable3").SmallGrid = False ActiveSheet.PivotTables("PivotTable3").AddFields RowFields:="FTG Crew", _ ColumnFields:="Data", PageFields:=Array("Calander Year", "Fiscal Year", _ "Month") ' ActiveSheet.PivotTables("PivotTable2").RefreshTabl e ' Application.StatusBar = "<<== BE PATIENT == PivotTable 2 Has Been Refreshed" ' ' Refresh PivotTable3 ' ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _ "'Job Costing Master Data'!R2C1:R" & LastRowOfJobCostingMasterData & "C35" ActiveSheet.PivotTables("PivotTable3").SmallGrid = False ActiveSheet.PivotTables("PivotTable3").AddFields RowFields:="FTG Crew", _ ColumnFields:="Data", PageFields:=Array("Calander Year", "Fiscal Year", _ "Month") ' ActiveSheet.PivotTables("PivotTable3").RefreshTabl e ' Application.StatusBar = "<<== BE PATIENT == PivotTable 3 Has Been Refreshed" ' ' Refresh PivotTable4 ' ActiveSheet.PivotTables("PivotTable4").PivotSelect "Builder[All]", xlLabelOnly ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _ "'Job Costing Master Data'!R2C1:R" & LastRowOfJobCostingMasterData & "C35" ActiveSheet.PivotTables("PivotTable4").SmallGrid = False ActiveSheet.PivotTables("PivotTable4").AddFields RowFields:="Builder", _ ColumnFields:="Data", PageFields:=Array("Calander Year", "Fiscal Year", _ "Month") ' ActiveSheet.PivotTables("PivotTable4").RefreshTabl e ' Application.StatusBar = "<<== BE PATIENT == PivotTable 4 Has Been Refreshed" ' ' Refresh PivotTable4 on Superintendant Sheet ' Sheets("Superintendant").Select ActiveSheet.PivotTables("PivotTable4").PivotSelect "Superintendant[All]", _ xlLabelOnly ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _ "'Job Costing Master Data'!R2C1:R" & LastRowOfJobCostingMasterData & "C35" ActiveSheet.PivotTables("PivotTable4").SmallGrid = False ActiveSheet.PivotTables("PivotTable4").AddFields RowFields:="Superintendant" _ , ColumnFields:="Data", PageFields:=Array("Calander Year", "Fiscal Year", _ "Month") ' ActiveSheet.PivotTables("PivotTable4").RefreshTabl e ' Application.StatusBar = "<<== BE PATIENT == PivotTable 4 On Superintendant Sheet Has Been Refreshed" ' ' ' Refresh PivotTable4 on Builder Sheet ' Sheets("Builder").Select ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _ "'Job Costing Master Data'!R2C1:R" & LastRowOfJobCostingMasterData & "C35" ActiveSheet.PivotTables("PivotTable4").SmallGrid = False ActiveSheet.PivotTables("PivotTable4").AddFields RowFields:="Builder", _ ColumnFields:="Data", PageFields:=Array("Calander Year", "Fiscal Year", _ "Month") ' ActiveSheet.PivotTables("PivotTable4").RefreshTabl e ' Application.StatusBar = "<<== BE PATIENT == PivotTable 4 On Builder Sheet Has Been Refreshed" ' ' Refresh PivotTable1 on FreeForm Analysis Sheet ' Sheets("FreeForm Analysis Table").Select ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _ "'Job Costing Master Data'!R2C1:R" & LastRowOfJobCostingMasterData & "C35" ActiveSheet.PivotTables("PivotTable1").SmallGrid = False ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:="Wall Crew", _ ColumnFields:="Data", PageFields:=Array("Builder", "Calander Year", _ "Fiscal Year", "Month") I would have preferred the simpler .RefreshTable solution for each pivot table, but I was getting spurrious results with following code. ActiveSheet.PivotTables("MASTER_PIVOT_DATA").Refre shTable Is my bad vb code suddenly causing this problem ? thanks in advance, tim |
Ho to Delete "Ghost" Pivot Tables
Sometimes I've found that having special formatting on a pivot table will
stay after the pivot table leaves, leaving an imprint of a table when there's no actual table. I have experienced an actual table that has shown up when trying to create a second table near another one, where the new table would have crossed over the old table. It does give you an error, but I think there may be some bugs there that leave some ghosts around. "needyourhelp" wrote: Thanks Dave. I guess I don't think sometimes. Any idea where they came from ? tim "Dave Breitenbach" wrote: highlight the area surronuding the pivot table and do edit, clear, all "needyourhelp" wrote: Hi, I use Office 2000. I have one spreadsheet that has several sheets with pivot tables. One sheet has multiple pivot tables. Today, I suddenly have extra, unwanted, blank (hence ghost) pivot tables inserted in 2 of my sheets. I've been using these sheets for about a year with no previous problems. I have no idea how these "ghost" pivot tables suddenly showed up in my sheets. I can't find a way to delete them. Question : How do I get rid of them ? I do have some VB code that spools through the sheets/tables to do a "refresh". It is not very effeceint, but it seems to work fine. ' ' Refresh The Master_Pivot_DATA Table ' Sheets("P&L").Activate ActiveSheet.PivotTables("MASTER_PIVOT_DATA").Pivot Select "'Job Number'[All]", _ xlLabelOnly ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _ "'Job Costing Master Data'!R2C1:R" & LastRowOfJobCostingMasterData & "C35" ActiveSheet.PivotTables("MASTER_PIVOT_DATA").Small Grid = False ActiveSheet.PivotTables("MASTER_PIVOT_DATA").AddFi elds RowFields:= _ "Job Number", ColumnFields:="Data", PageFields:=Array("Calander Year", _ "Fiscal Year", "Month", "Superintendant", "Wall Crew", "FTG Crew") Range("D1").Select ' ActiveSheet.PivotTables("MASTER_PIVOT_DATA").Refre shTable ' Application.StatusBar = "<<== BE PATIENT == The MASTER_PIVOT_DATA Table Has Been Refreshed" ' ' Refresh PivotTable1 ' ActiveSheet.PivotTables("PivotTable1").PivotSelect "Superintendant[All]", _ xlLabelOnly ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _ "'Job Costing Master Data'!R2C1:R" & LastRowOfJobCostingMasterData & "C35" ActiveSheet.PivotTables("PivotTable1").SmallGrid = False ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:="Superintendant" _ , ColumnFields:="Data", PageFields:=Array("Calander Year", "Fiscal Year", _ "Month") ' ActiveSheet.PivotTables("PivotTable1").RefreshTabl e ' Application.StatusBar = "<<== BE PATIENT == PivotTable 1 Has Been Refreshed" ' ' Refresh PivotTable2 ' ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _ "'Job Costing Master Data'!R2C1:R" & LastRowOfJobCostingMasterData & "C35" ActiveSheet.PivotTables("PivotTable2").SmallGrid = False ActiveSheet.PivotTables("PivotTable2").AddFields RowFields:="Wall Crew", _ ColumnFields:="Data", PageFields:=Array("Calander Year", "Fiscal Year", _ "Month") ActiveSheet.PivotTables("PivotTable3").PivotSelect "'FTG Crew'[All]", _ xlLabelOnly ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _ "'Job Costing Master Data'!R2C1:R1323C35" ActiveSheet.PivotTables("PivotTable3").SmallGrid = False ActiveSheet.PivotTables("PivotTable3").AddFields RowFields:="FTG Crew", _ ColumnFields:="Data", PageFields:=Array("Calander Year", "Fiscal Year", _ "Month") ' ActiveSheet.PivotTables("PivotTable2").RefreshTabl e ' Application.StatusBar = "<<== BE PATIENT == PivotTable 2 Has Been Refreshed" ' ' Refresh PivotTable3 ' ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _ "'Job Costing Master Data'!R2C1:R" & LastRowOfJobCostingMasterData & "C35" ActiveSheet.PivotTables("PivotTable3").SmallGrid = False ActiveSheet.PivotTables("PivotTable3").AddFields RowFields:="FTG Crew", _ ColumnFields:="Data", PageFields:=Array("Calander Year", "Fiscal Year", _ "Month") ' ActiveSheet.PivotTables("PivotTable3").RefreshTabl e ' Application.StatusBar = "<<== BE PATIENT == PivotTable 3 Has Been Refreshed" ' ' Refresh PivotTable4 ' ActiveSheet.PivotTables("PivotTable4").PivotSelect "Builder[All]", xlLabelOnly ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _ "'Job Costing Master Data'!R2C1:R" & LastRowOfJobCostingMasterData & "C35" ActiveSheet.PivotTables("PivotTable4").SmallGrid = False ActiveSheet.PivotTables("PivotTable4").AddFields RowFields:="Builder", _ ColumnFields:="Data", PageFields:=Array("Calander Year", "Fiscal Year", _ "Month") ' ActiveSheet.PivotTables("PivotTable4").RefreshTabl e ' Application.StatusBar = "<<== BE PATIENT == PivotTable 4 Has Been Refreshed" ' ' Refresh PivotTable4 on Superintendant Sheet ' Sheets("Superintendant").Select ActiveSheet.PivotTables("PivotTable4").PivotSelect "Superintendant[All]", _ xlLabelOnly ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _ "'Job Costing Master Data'!R2C1:R" & LastRowOfJobCostingMasterData & "C35" ActiveSheet.PivotTables("PivotTable4").SmallGrid = False ActiveSheet.PivotTables("PivotTable4").AddFields RowFields:="Superintendant" _ , ColumnFields:="Data", PageFields:=Array("Calander Year", "Fiscal Year", _ "Month") ' ActiveSheet.PivotTables("PivotTable4").RefreshTabl e ' Application.StatusBar = "<<== BE PATIENT == PivotTable 4 On Superintendant Sheet Has Been Refreshed" ' ' ' Refresh PivotTable4 on Builder Sheet ' Sheets("Builder").Select ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _ "'Job Costing Master Data'!R2C1:R" & LastRowOfJobCostingMasterData & "C35" ActiveSheet.PivotTables("PivotTable4").SmallGrid = False ActiveSheet.PivotTables("PivotTable4").AddFields RowFields:="Builder", _ ColumnFields:="Data", PageFields:=Array("Calander Year", "Fiscal Year", _ "Month") ' ActiveSheet.PivotTables("PivotTable4").RefreshTabl e ' Application.StatusBar = "<<== BE PATIENT == PivotTable 4 On Builder Sheet Has Been Refreshed" ' ' Refresh PivotTable1 on FreeForm Analysis Sheet ' Sheets("FreeForm Analysis Table").Select ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _ "'Job Costing Master Data'!R2C1:R" & LastRowOfJobCostingMasterData & "C35" ActiveSheet.PivotTables("PivotTable1").SmallGrid = False ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:="Wall Crew", _ ColumnFields:="Data", PageFields:=Array("Builder", "Calander Year", _ "Fiscal Year", "Month") I would have preferred the simpler .RefreshTable solution for each pivot table, but I was getting spurrious results with following code. ActiveSheet.PivotTables("MASTER_PIVOT_DATA").Refre shTable Is my bad vb code suddenly causing this problem ? thanks in advance, tim |
| All times are GMT +1. The time now is 09:29 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com