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