none
VBA - Pivot Cache error RRS feed

  • Question

  • Hi There,

    I am trying to write code for creating a pivot table in an existing spreadsheet using a dynamic range but keep getting a runtime error 5 and can't seem to get it to work. This is taking way longer than it should and getting frustrated...any input would be great.

       

    Sub Create_Pivot()
    '
    ' Create_Pivot Macro
    '
    With Application
            .ScreenUpdating = False
            .EnableEvents = False
        End With
    '
       Dim myFirstRow As Long
       Dim myLastRow As Long
       Dim myFirstColumn As Long
       Dim myLastColumn As Long
       Dim mySourceData As String
       Dim myDestinationRange As String
       Dim mySourceWorksheet As Worksheet
       Dim myDestinationWorksheet As Worksheet
       Dim myPivotTable As PivotTable

       With ThisWorkbook
            Set mySourceWorksheet = .Worksheets("Sum_Data")
            Set myDestinationWorksheet = .Worksheets("Pivot")
        End With
    myDestinationRange = myDestinationWorksheet.Range("A1").Address(ReferenceStyle:=xlR1C1)
    myFirstRow = 1
    myFirstColumn = 1

    With mySourceWorksheet.Cells

    myLastRow = .Find(What:="*", LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    myLastColumn = .Find(What:="*", LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column

    mySourceData = .Range(.Cells(myFirstRow, myFirstColumn), .Cells(myLastRow, myLastColumn)).Address(ReferenceStyle:=xlR1C1)

    End With

    Set myPivotTable = ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=mySourceWorksheet.Name & mySourceData).CreatePivotTable(TableDestination:=myDestinationWorksheet.Name & myDestinationRange, TableName:="PivotTableNew")

    With myPivotTable
    With .PivotFields("Class").Orientation = xlRowField
    With .PivotFields("Jan-18")
    .Orientation = xlDataField
    .Position = 1
    .Functiom = xlSum
    .NumberFormat = "#,##0.00"
    End With
    End With

             With Application
            .ScreenUpdating = True
            .EnableEvents = True
        End With
        End With
    End Sub

    There error is happening on the cache

    Friday, September 29, 2017 7:13 PM

All replies

  • Hi Candacem,

    For your issue, it is caused by invalid parameter for SourceData. It should be Sum_Data!R1C1:R5C5.

    I suggest you make a test with below code:

    Sub Create_Pivot()
    '
    ' Create_Pivot Macro
    '
    With Application
            .ScreenUpdating = False
            .EnableEvents = False
    End With
    '
       Dim myFirstRow As Long
       Dim myLastRow As Long
       Dim myFirstColumn As Long
       Dim myLastColumn As Long
       Dim mySourceData As String
       Dim myDestinationRange As String
       Dim mySourceWorksheet As Worksheet
       Dim myDestinationWorksheet As Worksheet
       Dim myPivotTable As PivotTable
    
       With ThisWorkbook
            Set mySourceWorksheet = .Worksheets("Sum_Data")
            Set myDestinationWorksheet = .Worksheets("Pivot")
        End With
    myDestinationRange = myDestinationWorksheet.Range("A1").Address(ReferenceStyle:=xlR1C1)
    myFirstRow = 1
    myFirstColumn = 1
    
    With mySourceWorksheet.Cells
    
    myLastRow = .Find(What:="*", LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    myLastColumn = .Find(What:="*", LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
    
    'mySourceData = .Range(.Cells(myFirstRow, myFirstColumn), .Cells(myLastRow, myLastColumn)).Address(ReferenceStyle:=xlR1C1)
    mySourceData = .Range(.Cells(1, 1), .Cells(5, 5)).Address(ReferenceStyle:=xlR1C1)
    
    End With
    Set myPivotTable = ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=mySourceWorksheet.Name & "!" & mySourceData) _
              .CreatePivotTable(TableDestination:=myDestinationWorksheet.Name & "!" & myDestinationRange, TableName:="PivotTableNew")
    
    With myPivotTable
    With .PivotFields("Class").Orientation = xlRowField
    With .PivotFields("Jan-18")
    .Orientation = xlDataField
    .Position = 1
    .Functiom = xlSum
    .NumberFormat = "#,##0.00"
    End With
    End With
    
             With Application
            .ScreenUpdating = True
            .EnableEvents = True
        End With
        End With
    End Sub
    

    Best Regards,

    Edward


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, October 2, 2017 6:32 AM