Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
needyourhelp
 
Posts: n/a
Default 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
  #2   Report Post  
Posted to microsoft.public.excel.misc
Dave Breitenbach
 
Posts: n/a
Default 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

  #3   Report Post  
Posted to microsoft.public.excel.misc
needyourhelp
 
Posts: n/a
Default 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

  #4   Report Post  
Posted to microsoft.public.excel.misc
Dave Breitenbach
 
Posts: n/a
Default 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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Problems with updating category names in pivot tables Vladimir Excel Discussion (Misc queries) 2 November 3rd 05 01:54 PM
How can I delete rows from Pivot Tables in Excel 2000 as in 97 Rex at B$4U Excel Discussion (Misc queries) 0 April 18th 05 04:55 AM
Macro for Pivot Tables Thomas Excel Discussion (Misc queries) 1 March 15th 05 02:03 AM
Product Function in Pivot Tables from Multiple Consolidation Range bbishop222 Excel Worksheet Functions 0 February 22nd 05 05:55 PM
How does the term 'pivot' apply to Excel's Pivot tables and Pivot. stvermont Excel Discussion (Misc queries) 1 February 17th 05 02:34 AM


All times are GMT +1. The time now is 08:56 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"