Ho to Delete "Ghost" Pivot Tables

N

needyourhelp

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").PivotSelect "'Job
Number'[All]", _
xlLabelOnly
ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _
"'Job Costing Master Data'!R2C1:R" & LastRowOfJobCostingMasterData &
"C35"
ActiveSheet.PivotTables("MASTER_PIVOT_DATA").SmallGrid = False
ActiveSheet.PivotTables("MASTER_PIVOT_DATA").AddFields 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").RefreshTable
'
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").RefreshTable
'
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").RefreshTable
'
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").RefreshTable
'
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").RefreshTable
'
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").RefreshTable
'
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").RefreshTable
'
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").RefreshTable

Is my bad vb code suddenly causing this problem ?

thanks in advance,

tim
 
D

Dave Breitenbach

highlight the area surronuding the pivot table and do edit, clear, all

needyourhelp said:
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").PivotSelect "'Job
Number'[All]", _
xlLabelOnly
ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _
"'Job Costing Master Data'!R2C1:R" & LastRowOfJobCostingMasterData &
"C35"
ActiveSheet.PivotTables("MASTER_PIVOT_DATA").SmallGrid = False
ActiveSheet.PivotTables("MASTER_PIVOT_DATA").AddFields 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").RefreshTable
'
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").RefreshTable
'
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").RefreshTable
'
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").RefreshTable
'
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").RefreshTable
'
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").RefreshTable
'
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").RefreshTable
'
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").RefreshTable

Is my bad vb code suddenly causing this problem ?

thanks in advance,

tim
Click to expand...
 
N

needyourhelp

Thanks Dave.

I guess I don't think sometimes.

Any idea where they came from ?

tim

Dave Breitenbach said:
highlight the area surronuding the pivot table and do edit, clear, all

needyourhelp said:
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").PivotSelect "'Job
Number'[All]", _
xlLabelOnly
ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _
"'Job Costing Master Data'!R2C1:R" & LastRowOfJobCostingMasterData &
"C35"
ActiveSheet.PivotTables("MASTER_PIVOT_DATA").SmallGrid = False
ActiveSheet.PivotTables("MASTER_PIVOT_DATA").AddFields 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").RefreshTable
'
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").RefreshTable
'
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").RefreshTable
'
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").RefreshTable
'
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").RefreshTable
'
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").RefreshTable
'
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").RefreshTable
'
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").RefreshTable

Is my bad vb code suddenly causing this problem ?

thanks in advance,

tim
Click to expand...
Click to expand...
 
D

Dave Breitenbach

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 said:
Thanks Dave.

I guess I don't think sometimes.

Any idea where they came from ?

tim

Dave Breitenbach said:
highlight the area surronuding the pivot table and do edit, clear, all

needyourhelp said:
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").PivotSelect "'Job
Number'[All]", _
xlLabelOnly
ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _
"'Job Costing Master Data'!R2C1:R" & LastRowOfJobCostingMasterData &
"C35"
ActiveSheet.PivotTables("MASTER_PIVOT_DATA").SmallGrid = False
ActiveSheet.PivotTables("MASTER_PIVOT_DATA").AddFields 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").RefreshTable
'
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").RefreshTable
'
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").RefreshTable
'
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").RefreshTable
'
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").RefreshTable
'
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").RefreshTable
'
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").RefreshTable
'
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").RefreshTable

Is my bad vb code suddenly causing this problem ?

thanks in advance,

tim
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Record Macro to Create Pivot Table 1
Passing string as array variable (Pivot VBA) 2
setting up a pivot table via macro? 0
Please Help! Need to add a Variable Range in my Excel Macro withPivot Tables 3
Pivot Table from Macro 2
Avoiding an extra sheet when creating a pivot table 3
Pivot table with a variable range in the sourcedata field 2
SourceData range in pivottables 1

Top