Results 1 to 3 of 3

Thread: [RESOLVED] Code to create pivot table - runtime error 1004

  1. #1

    Thread Starter
    Registered User
    Join Date
    Jul 2017
    Posts
    2

    Resolved [RESOLVED] Code to create pivot table - runtime error 1004

    Hello VB Gurus,

    Below is my code to build a pivot table with a dynamic range. I'm getting a runtime error 1004 - unable to get the pivot table property of the workclass sheet. The error comes up at line With ActiveSheet.PivotTables("PTable").PivotFields("Work Centre")

    Sub Create_Pivot()

    Dim PSheet As Worksheet
    Dim DSheet As Worksheet
    Dim PCache As PivotCache
    Dim PTable As PivotTable
    Dim PRange As Range
    Dim LastRow As Long
    Dim LastCol As Long

    'Set Pivot Table & Source Worksheet
    Set DSheet = Worksheets("Sheet1")
    Set PSheet = Worksheets("Pivot")

    'Defining Staring Point & Dynamic Range
    DSheet.Activate
    LastRow = DSheet.Cells(Rows.Count, 1).End(xlUp).Row
    LastCol = DSheet.Cells(1, Columns.Count).End(xlToLeft).Column
    Set PRange = DSheet.Cells(1, 1).Resize(LastRow, LastCol)


    Set PCache = ActiveWorkbook.PivotCaches.Create(xlDatabase, DSheet.Range("A1").CurrentRegion)

    Set PTable = PCache.CreatePivotTable(TableDestination:=PSheet.Cells(1, 1))

    With ActiveSheet.PivotTables("PTable").PivotFields("Work Centre")
    .Orientation = xlRowField
    .Position = 1
    End With
    With ActiveSheet.PivotTables("PTable").PivotFields("Status")
    .Orientation = xlRowField
    .Position = 2
    End With
    With ActiveSheet.PivotTables("PTable").PivotFields("Safety")
    .Orientation = xlColumnField
    .Position = 1
    End With
    ActiveSheet.PivotTables("PTable").AddDataField ActiveSheet.PivotTables( _
    "PTable").PivotFields("System status"), "Count of System status", xlCount
    End Sub

    Thanks in advance for your help

  2. #2
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Code to create pivot table - runtime error 1004

    why would you be using the activesheet object when you already have a sheet object for your pivot table sheet?

    as far as i can tell the activesheet, at that point, is not the one with the pivot tables, but dsheet, hence the error

    what is prange for? it is assigned a value, but not used within the code posted

    pls use code tags when posting code
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  3. #3

    Thread Starter
    Registered User
    Join Date
    Jul 2017
    Posts
    2

    Re: Code to create pivot table - runtime error 1004

    Thanks Westconn1 for replying to my post.

    The code was taken from blog site and when the blog's author didn't replay to my question, I posted it here. Following on from your feedback I've re-recorded the macro and made the changes I needed to give the dynamic range. The macro now works.

    Thanks again for your help.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width