Solved: Pivot Table Via Macro

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

rconverse

Thread Starter
Joined
Sep 7, 2007
Messages
191
Hello,

I am trying to record a macro that will create a pivot table based on a
spreadsheet.

I am receiving error Num: 1004

Unable to get the PivotField property of the PivotTable Class.

The code that recorded is below. I am much more familiar with coding in
Access, so don't even know where to start here.

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"tempPO_ExpSumm!C1:C4").CreatePivotTable TableDestination:="",
TableName:= _
"PivotTable4", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
With ActiveSheet.PivotTables("PivotTable4").PivotFields("Expeditor")
.Orientation = xlColumnField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable4").PivotFields("LS_Date")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable4").PivotFields("PO_Type")
.Orientation = xlRowField
.Position = 2
End With
ActiveSheet.PivotTables("PivotTable4").AddDataField
ActiveSheet.PivotTables( _
"PivotTable4").PivotFields("PO_Count"), "Count of PO_Count", xlCount

Thank you,
Roger
 
Joined
Jul 25, 2004
Messages
5,458
Are you sure all of those objects (i.e. worksheets) exist? Does the pivot table already exist? Since you're trying to create a new one I would test for existence of that specific name first (seeing as how you are using a specific text string to name the PT). Here is a function to test the existence of a named pivot table....

Code:
Function PivotTableExists(pvtName As String, Optional wks As Worksheet) As Boolean
    PivotTableExists = False
    If wks Is Nothing Then
        If ActiveSheet Is Nothing Then Exit Function
        Set wks = ActiveSheet
    End If
    On Error Resume Next
    PivotTableExists = Len(wks.PivotTables(pvtName).Name)
End Function

Sub TestIt()
    Dim pt As PivotTable, ptName As String
    ptName = "PivotTable4"
    If PivotTableExists(ptName, ActiveSheet) = True Then
        Set pt = ActiveSheet.PivotTables(ptName)
        'Alternatively you can delete the table here and just start fresh
    Else
        'The below line will fail, but it is where you create the pivot table
        Set pt = activesheet.PivotTables("PivotTable1").PivotCache...
    End If
End Sub
 

rconverse

Thread Starter
Joined
Sep 7, 2007
Messages
191
The pivot table would not exist at the time the macro is run. I think part ofmy issue is the fact that I do not add a worksheet for the pivot table to be created in. I am looking into it further now.

Thanks,
Roger
 

rconverse

Thread Starter
Joined
Sep 7, 2007
Messages
191
firefytr said:
Are you sure all of those objects (i.e. worksheets) exist? Does the pivot table already exist? Since you're trying to create a new one I would test for existence of that specific name first (seeing as how you are using a specific text string to name the PT). Here is a function to test the existence of a named pivot table....

Code:
Function PivotTableExists(pvtName As String, Optional wks As Worksheet) As Boolean
    PivotTableExists = False
    If wks Is Nothing Then
        If ActiveSheet Is Nothing Then Exit Function
        Set wks = ActiveSheet
    End If
    On Error Resume Next
    PivotTableExists = Len(wks.PivotTables(pvtName).Name)
End Function

Sub TestIt()
    Dim pt As PivotTable, ptName As String
    ptName = "PivotTable4"
    If PivotTableExists(ptName, ActiveSheet) = True Then
        Set pt = ActiveSheet.PivotTables(ptName)
        'Alternatively you can delete the table here and just start fresh
    Else
        'The below line will fail, but it is where you create the pivot table
        Set pt = activesheet.PivotTables("PivotTable1").PivotCache...
    End If
End Sub
Here is what I have thus far:

There are a total of 4 columns that I am trying to pivot. When I step through the macro, the pivot table is created except only one of the four field fields is available in the field list. Therefore, when it goes to "drag" one of the items that is not on the field list to the pivot table, it can't find the field name.

I am guessing that somewhere in here, I need to specify the number of field names or something, because it is after this step where the macro debugs.

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"tempPO_ExpSumm!C1:C4").CreatePivotTable TableDestination:="", TableName:= _
"PivotTable6", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select

Any additional assistance would be greatly appreciated.

Thank you,
Roger
 
Joined
Jul 25, 2004
Messages
5,458
Your range is only one column...

Code:
"tempPO_ExpSumm!C1:C4"
Wouldn't you need to encompass the entire range?
 

rconverse

Thread Starter
Joined
Sep 7, 2007
Messages
191
firefytr said:
Your range is only one column...

Code:
"tempPO_ExpSumm!C1:C4"
Wouldn't you need to encompass the entire range?
Yes, how do I do that, please? I am not used to VB in excel, so amnot too familiar with assigning ranges.

So I basically want to pull anything in columns A - D. Is that $A:$D - "tempPO_ExpSumm!$A:$D"?

Thanks,
Roger
 
Joined
Jul 25, 2004
Messages
5,458
Well I don't know what your range is. I'm assuming it starts in C1 and goes to at least column F (you said four columns), but I don't know what row. Is the row dynamic? Here is a static range...

Code:
"tempPO_ExpSumm!C1:F20"
To have a dynamic range, maybe ...

Code:
"tempPO_ExpSumm!C1:F" & cells(rows.count, "F").end(xlup).row
Note this is not a complete portion of code as it does not reference anything but the activesheet. It will work, but keep that in mind. It still needs to be integrated into your solution (i.e. explicitly reference the worksheet for prior to both the cells and the rows objects.
 

rconverse

Thread Starter
Joined
Sep 7, 2007
Messages
191
EXCELLENT!

Thank you very, very much. I finally got it to run all the way through.

Thank you,
Roger
 
Joined
Jul 25, 2004
Messages
5,458
You're very welcome. :)

If this solves it for you, don't forget to mark your thread as Solved by going to Thread Tools | Mark Solved | Perform Action.
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Members online

Top