-
Jul 30th, 2017, 09:07 PM
#1
Thread Starter
Registered User
[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
-
Jul 31st, 2017, 03:59 AM
#2
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
-
Aug 2nd, 2017, 08:29 PM
#3
Thread Starter
Registered User
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|