Run-time error '1004' to unset the "refresh on open"

[gio123bg]

Hi All,
I have an issue in executing this VBA code

Sheets("Piano 2Q 2011").Select
Range("c14").Select
ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:="table" <<<<<< error here

More exactly, to avoid the refresh of all pivots when I open the Workbook, I have unset the "refresh on open" via the PivotTable Options.

In this way I can open my workbook quickly without waiting the refresh of all pivots but the VBA code returns an error.

Is it possible, via VBA code, to set again this option?

Any help will be well appreciated.

Thanks in advance for your kind support.

Regards,

Giovanni

 

[Andrew Poulsom]

Try using the Add method of the PivotCaches object. From the macro recorder:

    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:="table") _
        .CreatePivotTable TableDestination:="[Book2]Sheet1!R1C4", TableName:= _
        "PivotTable1", DefaultVersion:=xlPivotTableVersion10

 

[gio123bg]

Hi!
Thanks a lot for your reply and help. I'm trying to understand the context where your VBA code can be included. More exactly, for example, before the VBA code where the error is raised.

<<<< here the new code?
Sheets("Piano 2Q 2011").Select
Range("c14").Select
ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:="table" <<<<<< error

Then I see static values

TableDestination:="[Book2]Sheet1!R1C4", TableName:= _
"PivotTable1",

TableDestination and TableName. I have many pivot tables and I need (maybe...) to loop to set the values.

Any suggestion will be well appreciated.

Thanks in advance for your kind support.

Regards,

Giovanni

 

[Andrew Poulsom]

Please post all your code with details of the error that you are getting.

 

[gio123bg]

Hi Andrew,
first of all many thanks for your kind cooperation and support.

Here are my steps.

1) Unset auto-refresh for all pivots (PivotTable Wizard --> Options --> (unset check box "Refresh on open"))

2) Code included on a "CommandButton"

Public pivdate As Date



Sub Macro() <<<<< The VBA code, after pressing the button, starts from here
MsgBox ("Start")
Call define_Range
Call Updata_Range

End Sub

Sub define_Range()


Dim Rng1 As Range
Sheets("Data").Visible = True
Sheets("Data").Select

'Change the range of cells (A1:B15) to be the range of cells you want to define
Set Rng1 = Sheets("Data").Range("A1")
ActiveWorkbook.Names.Add Name:="table", RefersTo:=Rng1
Range("Table").Select
' 'Range("Table").ClearContents

Selection.CurrentRegion.Name = "Table"
'End If
Range("A1").Select

End Sub
Sub Updata_Range()
Sheets("Piano 2Q 2011").Select
Range("c14").Select
ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:="table" <<<<< ERROR!!!!!
----------------------------------------------------------------------------------------------
(message box received)

Run-time error '1004';

The PivotTable report was saved without the underlying data. Use the Refresh Data command to update the report.

----------------------------------------------------------------------------------------------
'ActiveWorkbook.ShowPivotTableFieldList = True
Call delete_pivot_datafield
Call AddcolumninPivot
Call sum
Call ChangeDataCaptions
Call HiddenData
ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh

Sheets("Piano 2Q 2011 with Customer").Select
Range("c14").Select
ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:="table"
Call delete_pivot_datafield
Call AddcolumninPivot
Call sum
Call ChangeDataCaptions
Call HiddenData

ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh

Sheets("Piano 2Q 2011 VS Target").Select
Range("c4").Select
ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:="table"
Call delete_pivot_datafield2
Call Addcolumnpivot2
Call sum
Call ChangeDataCaptions
Call HiddenPivotData
ActiveSheet.PivotTables("PivotTable2").PivotCache.Refresh

Sheets("Piano 3Q 2011").Select
Range("c14").Select
ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:="table"
Call delete_pivot_datafield
Call AddcolumninPivot
Call sum
Call ChangeDataCaptions
Call HiddenData_3Q
ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh

Sheets("Piano 3Q 2011 with Customer").Select
Range("c14").Select
ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:="table"
Call delete_pivot_datafield
Call AddcolumninPivot
Call sum
Call ChangeDataCaptions
Call HiddenData_3Q
ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh

Sheets("AMS PV").Select
Range("c14").Select
ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:="table"
Call delete_pivot_datafield
Call AddcolumninPivot
Call sum
Call ChangeDataCaptions
Call HiddenData
ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh

Sheets("AI PV").Select
Range("c14").Select
ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:="table"
Call delete_pivot_datafield
Call AddcolumninPivot
Call sum
Call ChangeDataCaptions
Call HiddenData
ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh

Sheets("S&T PV").Select
Range("c14").Select
ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:="table"
Call delete_pivot_datafield
Call AddcolumninPivot
Call sum
Call ChangeDataCaptions
Call HiddenData
ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh

Sheets("SAP PV").Select
Range("c14").Select
ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:="table"
Call delete_pivot_datafield
Call AddcolumninPivot
Call sum
Call ChangeDataCaptions
Call HiddenData
ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh

Sheets("Oracle PV").Select
Range("c14").Select
ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:="table"
Call delete_pivot_datafield
Call AddcolumninPivot
Call sum
Call ChangeDataCaptions
Call HiddenData
ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh

Sheets("BAO PV").Select
Range("c14").Select
ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:="table"
Call delete_pivot_datafield
Call AddcolumninPivot
Call sum
Call ChangeDataCaptions
Call HiddenData
ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
End Sub

Sub DeleteMissingItems2002All()
'prevents unused items in non-OLAP PivotTables

Dim PT As PivotTable
Dim ws As Worksheet
Dim pc As PivotCache

Dim pf As PivotField
'change the settings
For Each ws In ActiveWorkbook.Worksheets
For Each PT In ws.PivotTables
For Each pf In PT.DataFields
'Function = xlSum
'pt.PivotCache.MissingItemsLimit = xlMissingItemsNone

pf.Function = xlSum
Next pf
Next PT
Next ws

'refresh all the pivot caches
For Each pc In ActiveWorkbook.PivotCaches
On Error Resume Next
pc.Refresh
Next pc

End Sub
Sub sum()


Dim pf As PivotField
For Each pf In ActiveSheet.PivotTables(1).DataFields
pf.Function = xlSum
Next pf

End Sub
Sub ChangeDataCaptions()
Dim PT As PivotTable
Dim pf As PivotField

Set PT = ActiveSheet.PivotTables(1)

For Each pf In PT.DataFields
If Left(pf.Caption, 6) = "Sum of" Then
pf.Caption = pf.SourceName & " "
End If
Next pf

End Sub

Sub delete_pivot_datafield()
Dim PT As PivotTable, ptField As PivotField
Set PT = ActiveSheet.PivotTables("PivotTable1")
For Each ptField In PT.DataFields
ptField.Orientation = xlHidden
Next ptField
Set PT = Nothing

End Sub

Sub delete_pivot_datafield2()
Dim PT As PivotTable, ptField As PivotField
Set PT = ActiveSheet.PivotTables("PivotTable2")
For Each ptField In PT.DataFields
ptField.Orientation = xlHidden
Next ptField
Set PT = Nothing

End Sub

Sub delete()

Dim PT As PivotTable
Dim ws As Worksheet
Dim pc As PivotCache

Dim pf As PivotField
'change the settings
For Each ws In ActiveWorkbook.Worksheets
For Each PT In ws.PivotTables
For Each pf In PT.DataFields
'Function = xlSum
'pt.PivotCache.MissingItemsLimit = xlMissingItemsNone
pf.Orientation = xlHidden
pf.Function = xlSum
Next pf
Next PT
Next ws

End Sub
Sub HiddenData()
Dim StartTime As Date
pivdate = #6/24/2011#
StartTime = Format(pivdate, "mm/dd/yyyy")

Dim pvtFiled As PivotField
Dim pvtTable As PivotTable
Set pvtTable = ActiveSheet.PivotTables("PivotTable1")
For Each pvtField In pvtTable.DataFields
If pvtField > StartTime Then
pvtField.Orientation = xlHidden
End If

Next pvtField
End Sub
Sub HiddenData_3Q()
Dim StartTime As Date
pivdate = #9/23/2011#
StartTime = Format(pivdate, "mm/dd/yyyy")

Dim pvtFiled As PivotField
Dim pvtTable As PivotTable
Set pvtTable = ActiveSheet.PivotTables("PivotTable1")
For Each pvtField In pvtTable.DataFields
If pvtField > StartTime Then
pvtField.Orientation = xlHidden
End If

Next pvtField
End Sub
Sub HiddenPivotData()
Dim StartTime As Date
Dim sivdate As Date
sivdate = #6/24/2011#
StartTime = Format(sivdate, "mm/dd/yyyy")

Dim pvtFiled As PivotField
Dim pvtTable As PivotTable
Set pvtTable = ActiveSheet.PivotTables("PivotTable2")
For Each pvtField In pvtTable.DataFields
If pvtField > StartTime Then
pvtField.Orientation = xlHidden
End If


Next pvtField

End Sub

Sub AddcolumninPivot()
ActiveSheet.PivotTables("PivotTable1").PivotFields(Sheets("Data").Range("EK1").Value).Orientation = _
xlDataField
ActiveSheet.PivotTables("PivotTable1").PivotFields(Sheets("Data").Range("EL1").Value).Orientation = _
xlDataField
ActiveSheet.PivotTables("PivotTable1").PivotFields(Sheets("Data").Range("EM1").Value).Orientation = _
xlDataField
ActiveSheet.PivotTables("PivotTable1").PivotFields(Sheets("Data").Range("EN1").Value).Orientation = _
xlDataField
ActiveSheet.PivotTables("PivotTable1").PivotFields(Sheets("Data").Range("EO1").Value).Orientation = _
xlDataField
ActiveSheet.PivotTables("PivotTable1").PivotFields(Sheets("Data").Range("EP1").Value).Orientation = _
xlDataField
ActiveSheet.PivotTables("PivotTable1").PivotFields(Sheets("Data").Range("EQ1").Value).Orientation = _
xlDataField
ActiveSheet.PivotTables("PivotTable1").PivotFields(Sheets("Data").Range("ER1").Value).Orientation = _
xlDataField
ActiveSheet.PivotTables("PivotTable1").PivotFields(Sheets("Data").Range("ES1").Value).Orientation = _
xlDataField
ActiveSheet.PivotTables("PivotTable1").PivotFields(Sheets("Data").Range("ET1").Value).Orientation = _
xlDataField
ActiveSheet.PivotTables("PivotTable1").PivotFields(Sheets("Data").Range("EU1").Value).Orientation = _
xlDataField
ActiveSheet.PivotTables("PivotTable1").PivotFields(Sheets("Data").Range("EV1").Value).Orientation = _
xlDataField
ActiveSheet.PivotTables("PivotTable1").PivotFields(Sheets("Data").Range("EW1").Value).Orientation = _
xlDataField
ActiveSheet.PivotTables("PivotTable1").PivotFields(Sheets("Data").Range("EX1").Value).Orientation = _
xlDataField
ActiveSheet.PivotTables("PivotTable1").PivotFields(Sheets("Data").Range("EY1").Value).Orientation = _
xlDataField
ActiveSheet.PivotTables("PivotTable1").PivotFields(Sheets("Data").Range("EZ1").Value).Orientation = _
xlDataField
ActiveSheet.PivotTables("PivotTable1").PivotFields(Sheets("Data").Range("FA1").Value).Orientation = _
xlDataField
ActiveSheet.PivotTables("PivotTable1").PivotFields(Sheets("Data").Range("FB1").Value).Orientation = _
xlDataField
ActiveSheet.PivotTables("PivotTable1").PivotFields(Sheets("Data").Range("FC1").Value).Orientation = _
xlDataField


End Sub


Sub Addcolumnpivot2()
ActiveSheet.PivotTables("PivotTable2").PivotFields(Sheets("Data").Range("EK1").Value).Orientation = _
xlDataField
ActiveSheet.PivotTables("PivotTable2").PivotFields(Sheets("Data").Range("EL1").Value).Orientation = _
xlDataField
ActiveSheet.PivotTables("PivotTable2").PivotFields(Sheets("Data").Range("EM1").Value).Orientation = _
xlDataField
ActiveSheet.PivotTables("PivotTable2").PivotFields(Sheets("Data").Range("EN1").Value).Orientation = _
xlDataField
ActiveSheet.PivotTables("PivotTable2").PivotFields(Sheets("Data").Range("EO1").Value).Orientation = _
xlDataField
ActiveSheet.PivotTables("PivotTable2").PivotFields(Sheets("Data").Range("EP1").Value).Orientation = _
xlDataField
ActiveSheet.PivotTables("PivotTable2").PivotFields(Sheets("Data").Range("EQ1").Value).Orientation = _
xlDataField
ActiveSheet.PivotTables("PivotTable2").PivotFields(Sheets("Data").Range("ER1").Value).Orientation = _
xlDataField
ActiveSheet.PivotTables("PivotTable2").PivotFields(Sheets("Data").Range("ES1").Value).Orientation = _
xlDataField
ActiveSheet.PivotTables("PivotTable2").PivotFields(Sheets("Data").Range("ET1").Value).Orientation = _
xlDataField
ActiveSheet.PivotTables("PivotTable2").PivotFields(Sheets("Data").Range("EU1").Value).Orientation = _
xlDataField
ActiveSheet.PivotTables("PivotTable2").PivotFields(Sheets("Data").Range("EV1").Value).Orientation = _
xlDataField
ActiveSheet.PivotTables("PivotTable2").PivotFields(Sheets("Data").Range("EW1").Value).Orientation = _
xlDataField
ActiveSheet.PivotTables("PivotTable2").PivotFields(Sheets("Data").Range("EX1").Value).Orientation = _
xlDataField
ActiveSheet.PivotTables("PivotTable2").PivotFields(Sheets("Data").Range("EY1").Value).Orientation = _
xlDataField
ActiveSheet.PivotTables("PivotTable2").PivotFields(Sheets("Data").Range("EZ1").Value).Orientation = _
xlDataField
ActiveSheet.PivotTables("PivotTable2").PivotFields(Sheets("Data").Range("FA1").Value).Orientation = _
xlDataField
ActiveSheet.PivotTables("PivotTable2").PivotFields(Sheets("Data").Range("FB1").Value).Orientation = _
xlDataField
ActiveSheet.PivotTables("PivotTable2").PivotFields(Sheets("Data").Range("FC1").Value).Orientation = _
xlDataField
End Sub



Thanks in advance for your help.

Regards,

Giovanni

 

[Andrew Poulsom]

Check 'Save data with table layout' under Data options on the PivotTable Options dialog.

 

[gio123bg]

Hi,
Good!!!!! Problem solved!!! Thank you very much indeed!

Regards,

Giovanni