Pivot Table Error

[Anthony G.]

Hello to All…

Upon opening an existing file, I receive the following error: Unable to Read File….I then click OK, Enable the macros and upon doing so, the following message appears: Excel Table Report on [Sales] Sheet1 was discarded due to integrity problems.

I have two Pivot tables on the report – Pivot8 and Pivot1 – two separate worksheets but using the same source data. I have placed a button on each worksheet to enable the user to refresh the Pivot Table. Pivot1 is the table that is being discarded and when I click the button on Sheet1, the following Run time error 1004 appears: Unable to get the Pivot Tables property of the Worksheet class….when I select Debug, here’s the code with the bolded section being the point of error:

ActiveSheet.Unprotect Password:="test"
Range("K9").Select
ActiveCell.FormulaR1C1 = "=NOW()"
Range("K9").Select
Selection.Copy
Range("K9").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("h161").Select
ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
ActiveWorkbook.ShowPivotTableFieldList = False
Columns("I:I").Select
Selection.ColumnWidth = 14.86
Range("A19").Select
ActiveSheet.Protect Password:="test"
End Sub

I guess I understand why I’m getting the Run time error (because the PT no longer exists); question is, why is it being discarded in the first place…?!?

Any assistance in correcting this will be greatly appreciated.

Thank you…Anthony

 

[Anthony G.]

I've searched high and low for a reason as to why this can be happening and came up empty handed.

Anyone have any suggestions?

Thanks...Anthony

 

[BrianB]

Try :-

ActiveSheet.PivotTables("PivotTable1").RefreshTable

 

[Anthony G.]

Modifying the code at this point will not work because the Pivot Tables no longer exist. For some unknown reason, Excel is discarding them, so when the user attempts to refresh, an error will undoubtedly appear. Question is, why are they being discarded in the first place???

 

[BrianB]

Could be workbook corruption.

I have never found the need to make pivot tables on the fly. Wastes time. Much better to have the PT already set up, change the base data, and Refresh. The more work you get Excel to do, over time, tends to lead to corruption. One workbook that I use with 2 pivot tables has been in daily use for 2-3 years now with no problems.

 

[Anthony G.]

The Pivot Tables were already set up so that when the user enters/updates data in the source worksheet, a simple refresh would do the trick. I've just found out that those individuals who've have their PT's discarded/corrupted were the same users who had the IT folks re-configure their computers. Perhaps that's the root of the problem.

Anyway, thanks for the feedback.

Anthony

 

[Anthony G.]

I've done quite a bit of research on this and thought I had it licked...until a user just notified me of the same error again. This file consists of alot of code and only one Pivot Table....here's the error I'm getting. Any Suggestions??? Thanks - Anthony

Errors were detected in ‘Top Accounts_2004.xls’ but MS Excel was able to open the file by making the repairs listed below:
Pivot Table report ‘Pivot Table8 on ‘[Top Accounts_2004.xls]Sheet 15’ was discarded due to integrity problems.

By the way, this person has been using the report for a couple of weeks now and all of a sudden this error always appeared at the opening of the Workbook today.

 

[Anthony G.]

OK, I'm being a bit relentless with this Pivot Table thing, but I've created over 50 templates, sent them out and now I'm being flooded with phone calls becase Excel is discarding the table. Originally, the workbook had two Pivot Tables, but I have since discadred one and here's the code....could there be anything in the code that's causing the table to be discarded?

Thanks Again and Again and Again...Anthony!

Sub Pivot_Detail()


ActiveSheet.Unprotect Password:="abc"
ActiveSheet.PivotTables("PivotTable8").PivotCache.Refresh
Columns("B:B").Select
Selection.ColumnWidth = 17
Columns("C:C").Select
Selection.ColumnWidth = 27
Columns("D:D").Select
Selection.ColumnWidth = 30
Columns("E:E").Select
Selection.ColumnWidth = 26
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Columns("G:J").Select
Selection.ColumnWidth = 12
Range("B4").Select
ActiveSheet.PivotTables("PivotTable8").PivotSelect "Discipline[All;Total]", _
xlDataAndLabel, True
Selection.Interior.ColorIndex = 15
Selection.Font.Bold = False
Selection.Font.Bold = True
Selection.Interior.ColorIndex = 15
Range("C4").Select
ActiveCell.FormulaR1C1 = "=NOW()"
Range("C4").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("B6").Select
ActiveSheet.Protect Password:="abc"
Dim DoSave

DoSave = MsgBox("The updating process is now complete. Do you wish to save the report?", vbYesNo)

If DoSave = vbYes Then
ActiveWorkbook.Save
End If
End Sub

 

[Mark W.]

See http://support.microsoft.com/default.aspx?scid=kb;en-us;819853&Product=xl2002

 

[Anthony G.]

I guess being relentless sometimes pays off...!!

You're a Savior...Thanks!!!!!!!

Anthony