XL97 Pivot Table Unable to set the _default property

[vaneagle]

Hi,

I have an issue with some vba code.. when I use VBA on a pivot table it gives me the following message "Unable to set the _default property of the pivotitem class".

It errors on

ActiveSheet.PivotTables("PivotTable1").PivotFields("Department").CurrentPage = _
        "5"

.

my code is as below:

Sub Getric()
'
' Getric Macro
' 
'
    Dim week As String
    Dim weekstart As String
    Dim weekend As String
    Dim state As String
    Dim staten As String
    Dim weeksc As String
    Dim weekec As String
    Dim Month As String
    Dim JunkData As New DataObject ' Declare Variable to clear out clipboard
    Dim WB As Workbook
    Dim WB2 As Workbook
    Dim WB3 As Workbook
    
    'Application.ScreenUpdating = False
    Sheets("Menu").Select
    weekstart = Range("e6")
    weekend = Range("e8")
    weekend = weekend + 1
    'weekend = 8
    weeksc = Range("b6")
    weekec = Range("b8")
    Month = Range("e4")
    Set WB2 = Workbooks(Dir(ActiveWorkbook.FullName))
    Debug.Print WB2.FullName
    
    Do While weekstart < weekend
    
    Workbooks.Open FileName:=&quot;U:\Testing\Ric Data\RIC WK&quot; & weekstart & &quot;.xls&quot;, ReadOnly:=True
        
    Set WB3 = Workbooks(Dir(ActiveWorkbook.FullName))
    Debug.Print WB3.FullName
    
    Sheets(&quot;NewSum&quot;).Select
    
    If weeksc = 1 Then
        ActiveSheet.PivotTables(&quot;PivotTable1&quot;).PivotSelect &quot;&quot;, xlDataAndLabel
        ActiveSheet.PivotTables(&quot;PivotTable1&quot;).RefreshTable

        ActiveSheet.PivotTables("PivotTable1").PivotFields("Department").CurrentPage = _
        "5"

        ActiveSheet.PivotTables("PivotTable1").PivotSelect "", xlDataAndLabel
        Selection.Copy
        WB2.Activate
        Sheets("Week" & weeksc).Select
        Range("a1").Select
        Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
        WB3.Activate
        ActiveSheet.PivotTables("PivotTable1").PivotFields("Department").CurrentPage = _
        "10"
        ActiveSheet.PivotTables("PivotTable1").PivotSelect "", xlDataAndLabel
        Selection.Copy
        WB2.Activate
        Sheets("Week" & weeksc).Select
        Range("a18").Select
        Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
        WB3.Activate
        ActiveSheet.PivotTables("PivotTable1").PivotFields("Department").CurrentPage = _
        "15"
        ActiveSheet.PivotTables("PivotTable1").PivotSelect "", xlDataAndLabel
        Selection.Copy
        WB2.Activate
        Sheets("Week" & weeksc).Select
        Range("a35").Select
        Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
        WB3.Activate
        ActiveSheet.PivotTables("PivotTable1").PivotFields("Department").CurrentPage = _
        "20"
        ActiveSheet.PivotTables("PivotTable1").PivotSelect "", xlDataAndLabel
        Selection.Copy
        WB2.Activate
        Sheets("Week" & weeksc).Select
        Range("a52").Select
        Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
        WB3.Activate
        ActiveSheet.PivotTables("PivotTable1").PivotFields("Department").CurrentPage = _
        "27"
        ActiveSheet.PivotTables("PivotTable1").PivotSelect "", xlDataAndLabel
        Selection.Copy
        WB2.Activate
        Sheets("Week" & weeksc).Select
        Range("a67").Select
        Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
        WB3.Activate
        JunkData.SetText ""
        JunkData.PutInClipboard
        ActiveWorkbook.Close SaveChanges:=False
  
    ElseIf weeksc > 1 Then
        ActiveSheet.PivotTables("PivotTable1").PivotSelect "", xlDataAndLabel
        ActiveSheet.PivotTables("PivotTable1").RefreshTable
        ActiveSheet.PivotTables("PivotTable1").PivotFields("Department").CurrentPage = _
        "5"
        ActiveSheet.PivotTables("PivotTable1").PivotSelect "", xlDataAndLabel
        Selection.Copy
        WB2.Activate
        Sheets("Week" & weeksc).Select
        Range("a1").Select
        Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
        WB3.Activate
        ActiveSheet.PivotTables("PivotTable1").PivotFields("Department").CurrentPage = _
        "10"
        ActiveSheet.PivotTables("PivotTable1").PivotSelect "", xlDataAndLabel
        Selection.Copy
        WB2.Activate
        Sheets("Week" & weeksc).Select
        Range("a30").Select
        Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
        WB3.Activate
        ActiveSheet.PivotTables("PivotTable1").PivotFields("Department").CurrentPage = _
        "15"
        ActiveSheet.PivotTables("PivotTable1").PivotSelect "", xlDataAndLabel
        Selection.Copy
        WB2.Activate
        Sheets("Week" & weeksc).Select
        Range("a59").Select
        Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
        WB3.Activate
        ActiveSheet.PivotTables("PivotTable1").PivotFields("Department").CurrentPage = _
        "20"
        ActiveSheet.PivotTables("PivotTable1").PivotSelect "", xlDataAndLabel
        Selection.Copy
        WB2.Activate
        Sheets("Week" & weeksc).Select
        Range("a88").Select
        Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
        WB3.Activate
        ActiveSheet.PivotTables("PivotTable1").PivotFields("Department").CurrentPage = _
        "27"
        ActiveSheet.PivotTables("PivotTable1").PivotSelect "", xlDataAndLabel
        Selection.Copy
        WB2.Activate
        Sheets("Week" & weeksc).Select
        Range("a113").Select
        Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
        WB3.Activate
        JunkData.SetText ""
        JunkData.PutInClipboard
        ActiveWorkbook.Close SaveChanges:=False
    
    End If
    
    weekstart = weekstart + 1
    weeksc = weeksc + 1
    Loop
    
    WB2.Activate
    JunkData.SetText ""
    JunkData.PutInClipboard
    Sheets("Menu").Select
    
    ActiveWorkbook.SaveAs FileName:="U:\Testing\RIC Data " & Month & ".xls", _
        FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
        ReadOnlyRecommended:=False, CreateBackup:=False
    Application.DisplayAlerts = True
End Sub

any ideas ??

 

[TopJack]

I remember getting an error message like that a long time ago when I was doing something similar. The problem I had was the focus was not on the spreadsheet at the time the VBA run and the pivot table is not recognised. An example of this would that a command button has the focus from clicking, ie selected, and the sheet itself is not active.

To solve my problem I used this statement just before my update to the pivot table :-

ActiveCell.Activate

I hope this solves your problem aswell.

Good luck,
TopJack.