VBA Pivot Tables - Object defined error

**retrohead** · 09-26-2012, 12:05 PM

I have a sheet which is auto generated and I want to add pivot tables to it. The macros work fine individually but when I try to run all 3 macros in one go, the 3rd one will fail with an object-defined error no matter which order I run them in. Any ideas?

Option Explicit

Function addAttendancePivot()
    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
        "Attendance!$A1:$AB65536").CreatePivotTable TableDestination:="", TableName:= _
        "AttendancePivotTable", DefaultVersion:=xlPivotTableVersion10
        
    ActiveSheet.Name = "Attendance Summary"
    
    ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
    ActiveSheet.Cells(3, 1).Select
    
    With ActiveSheet.PivotTables("AttendancePivotTable").PivotFields("Extended Absence?")
        .Orientation = xlPageField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("AttendancePivotTable").PivotFields("Working Absence?")
        .Orientation = xlPageField
        .Position = 1
    End With
    
    ActiveSheet.PivotTables("AttendancePivotTable").AddDataField _
        ActiveSheet.PivotTables("AttendancePivotTable").PivotFields("Reason for Absence") _
        , "Count of reason for absence" _
        , xlCount
    
    With ActiveSheet.PivotTables("AttendancePivotTable").PivotFields("Reason for Absence")
        .Orientation = xlRowField
        .Position = 1
    End With
    
    
    ActiveSheet.PivotTables("AttendancePivotTable").PivotFields("Extended Absence?"). _
        CurrentPage = "No"
End Function

Function addAttendanceDisciplinaryPivot()
    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
        "Attendance!X1:X65536").CreatePivotTable TableDestination:="", TableName:= _
        "AttendanceDiscPivotTable", DefaultVersion:=xlPivotTableVersion10
    
    ActiveSheet.Name = "Attendance Disciplinary Summary"
        
    ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
    ActiveSheet.Cells(3, 1).Select
    ActiveWorkbook.ShowPivotTableFieldList = True
    ActiveSheet.PivotTables("AttendanceDiscPivotTable").AddDataField ActiveSheet.PivotTables( _
        "AttendanceDiscPivotTable").PivotFields("Disciplinary Level"), "Count of Disciplinary Level" _
        , xlCount
    With ActiveSheet.PivotTables("AttendanceDiscPivotTable").PivotFields("Disciplinary Level")
        .Orientation = xlRowField
        .Position = 1
    End With
End Function


Function addDisciplinaryPivot()
    
    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
        "Disciplinary!L1:L65536").CreatePivotTable TableDestination:="", TableName:= _
        "DiscPivotTable", DefaultVersion:=xlPivotTableVersion10
    
    ActiveSheet.Name = "Disciplinary Summary"
        
    ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
    ActiveSheet.Cells(3, 1).Select
    ActiveWorkbook.ShowPivotTableFieldList = True
    ActiveSheet.PivotTables("DiscPivotTable").AddDataField ActiveSheet.PivotTables( _
        "DiscPivotTable").PivotFields("Disciplinary Level"), "Count of Disciplinary Level" _
        , xlCount
    With ActiveSheet.PivotTables("DiscPivotTable").PivotFields("Disciplinary Level")
        .Orientation = xlRowField
        .Position = 1
    End With
End Function

Private Sub Option Explicit

Function addAttendancePivot()
    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
        "Attendance!$A1:$AB65536").CreatePivotTable TableDestination:="", TableName:= _
        "AttendancePivotTable", DefaultVersion:=xlPivotTableVersion10
        
    ActiveSheet.Name = "Attendance Summary"
    
    ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
    ActiveSheet.Cells(3, 1).Select
    
    With ActiveSheet.PivotTables("AttendancePivotTable").PivotFields("Extended Absence?")
        .Orientation = xlPageField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("AttendancePivotTable").PivotFields("Working Absence?")
        .Orientation = xlPageField
        .Position = 1
    End With
    
    ActiveSheet.PivotTables("AttendancePivotTable").AddDataField _
        ActiveSheet.PivotTables("AttendancePivotTable").PivotFields("Reason for Absence") _
        , "Count of reason for absence" _
        , xlCount
    
    With ActiveSheet.PivotTables("AttendancePivotTable").PivotFields("Reason for Absence")
        .Orientation = xlRowField
        .Position = 1
    End With
    
    
    ActiveSheet.PivotTables("AttendancePivotTable").PivotFields("Extended Absence?"). _
        CurrentPage = "No"
End Function

Function addAttendanceDisciplinaryPivot()
    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
        "Attendance!X1:X65536").CreatePivotTable TableDestination:="", TableName:= _
        "AttendanceDiscPivotTable", DefaultVersion:=xlPivotTableVersion10
    
    ActiveSheet.Name = "Attendance Disciplinary Summary"
        
    ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
    ActiveSheet.Cells(3, 1).Select
    ActiveWorkbook.ShowPivotTableFieldList = True
    ActiveSheet.PivotTables("AttendanceDiscPivotTable").AddDataField ActiveSheet.PivotTables( _
        "AttendanceDiscPivotTable").PivotFields("Disciplinary Level"), "Count of Disciplinary Level" _
        , xlCount
    With ActiveSheet.PivotTables("AttendanceDiscPivotTable").PivotFields("Disciplinary Level")
        .Orientation = xlRowField
        .Position = 1
    End With
End Function


Function addDisciplinaryPivot()
    
    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
        "Disciplinary!L1:L65536").CreatePivotTable TableDestination:="", TableName:= _
        "DiscPivotTable", DefaultVersion:=xlPivotTableVersion10
    
    ActiveSheet.Name = "Disciplinary Summary"
        
    ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
    ActiveSheet.Cells(3, 1).Select
    ActiveWorkbook.ShowPivotTableFieldList = True
    ActiveSheet.PivotTables("DiscPivotTable").AddDataField ActiveSheet.PivotTables( _
        "DiscPivotTable").PivotFields("Disciplinary Level"), "Count of Disciplinary Level" _
        , xlCount
    With ActiveSheet.PivotTables("DiscPivotTable").PivotFields("Disciplinary Level")
        .Orientation = xlRowField
        .Position = 1
    End With
End Function

Private Sub btnMacros_Click()
    addAttendancePivot
    addAttendanceDisciplinaryPivot
    addDisciplinaryPivot
End Sub()

This fails on the addDisciplinaryPivot part of the btnMacros_Click sub. Changing the order the pivot tables are made has the same effect (the 3rd pivot table will fail), for example this:

Private Sub btnMacros_Click()
    addAttendanceDisciplinaryPivot
    addDisciplinaryPivot
    addAttendancePivot
End Sub()

Fails on addAttendancePivot. I've spent a day on this now and it's wasting too much time. I could write my own pivot table faster than fixing this problem.

The lines that fail are the ones that include ActiveWorkbook.PivotCaches.Add

**retrohead** · 09-27-2012, 05:52 AM

Well I solved the problem (as random as it is).

Selecting a different sheet seems to work. At the end of each of the functions that create the pivots, I added this which now seems to work:

ActiveWorkbook.Sheets("Attendance").Select

Fixed code:

Private Sub Option Explicit

Function addAttendancePivot()
    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
        "Attendance!$A1:$AB65536").CreatePivotTable TableDestination:="", TableName:= _
        "AttendancePivotTable", DefaultVersion:=xlPivotTableVersion10
        
    ActiveSheet.Name = "Attendance Summary"
    
    ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
    ActiveSheet.Cells(3, 1).Select
    
    With ActiveSheet.PivotTables("AttendancePivotTable").PivotFields("Extended Absence?")
        .Orientation = xlPageField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("AttendancePivotTable").PivotFields("Working Absence?")
        .Orientation = xlPageField
        .Position = 1
    End With
    
    ActiveSheet.PivotTables("AttendancePivotTable").AddDataField _
        ActiveSheet.PivotTables("AttendancePivotTable").PivotFields("Reason for Absence") _
        , "Count of reason for absence" _
        , xlCount
    
    With ActiveSheet.PivotTables("AttendancePivotTable").PivotFields("Reason for Absence")
        .Orientation = xlRowField
        .Position = 1
    End With
    
    
    ActiveSheet.PivotTables("AttendancePivotTable").PivotFields("Extended Absence?"). _
        CurrentPage = "No"
    ActiveWorkbook.Sheets("Attendance").Select
End Function

Function addAttendanceDisciplinaryPivot()
    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
        "Attendance!X1:X65536").CreatePivotTable TableDestination:="", TableName:= _
        "AttendanceDiscPivotTable", DefaultVersion:=xlPivotTableVersion10
    
    ActiveSheet.Name = "Attendance Disciplinary Summary"
        
    ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
    ActiveSheet.Cells(3, 1).Select
    ActiveWorkbook.ShowPivotTableFieldList = True
    ActiveSheet.PivotTables("AttendanceDiscPivotTable").AddDataField ActiveSheet.PivotTables( _
        "AttendanceDiscPivotTable").PivotFields("Disciplinary Level"), "Count of Disciplinary Level" _
        , xlCount
    With ActiveSheet.PivotTables("AttendanceDiscPivotTable").PivotFields("Disciplinary Level")
        .Orientation = xlRowField
        .Position = 1
    End With
    ActiveWorkbook.Sheets("Attendance").Select
End Function


Function addDisciplinaryPivot()
    
    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
        "Disciplinary!L1:L65536").CreatePivotTable TableDestination:="", TableName:= _
        "DiscPivotTable", DefaultVersion:=xlPivotTableVersion10
    
    ActiveSheet.Name = "Disciplinary Summary"
        
    ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
    ActiveSheet.Cells(3, 1).Select
    ActiveWorkbook.ShowPivotTableFieldList = True
    ActiveSheet.PivotTables("DiscPivotTable").AddDataField ActiveSheet.PivotTables( _
        "DiscPivotTable").PivotFields("Disciplinary Level"), "Count of Disciplinary Level" _
        , xlCount
    With ActiveSheet.PivotTables("DiscPivotTable").PivotFields("Disciplinary Level")
        .Orientation = xlRowField
        .Position = 1
    End With
    ActiveWorkbook.Sheets("Attendance").Select
End Function

Private Sub btnMacros_Click()
    addAttendancePivot
    addAttendanceDisciplinaryPivot
    addDisciplinaryPivot
End Sub()

VBA Pivot Tables - Object defined error

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

VBA Pivot Tables - Object defined error

Re: VBA Pivot Tables - Object defined error

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions