Runtime Error 1004 on Macro

[Mikeykt]

Hi

I keep getting a Runtime Error 1004 on the following Macro,

It keeps failing at the highlighted area. Anybody have any ideas?

Thanks

Dim wksPivot As Worksheet
Dim wksData As Worksheet
Dim pc As PivotCache
Dim PT As PivotTable
Set wksPivot = Sheets("RSPIVOT")
Set wksData = Sheets("Inactive RG by Recruit Type 2")
wksPivot.UsedRange.Clear
With wksData
Set pc = .Parent.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"'" & .Name & "'!" & .Range("A1").CurrentRegion.Address(0, 0, xlR1C1))
Set PT = pc.CreatePivotTable(TableDestination:=wksPivot.Cells(3, 1), TableName:="")
End With
With PT
With .PivotFields("Behaviour")
.Orientation = xlPageField
.Position = 1
End With
With .PivotFields("Value")
.Orientation = xlPageField
.Position = 1
End With
With .PivotFields("Recency")
.Orientation = xlPageField
.Position = 1
End With
With .PivotFields("Segment")
.Orientation = xlRowField
.Position = 1
End With
With .PivotFields("Recruitment Summary")
.Orientation = xlColumnField
.Position = 1
End With
With .PivotFields("Recruitment Source")
.Orientation = xlColumnField
.Position = 2
End With
.AddDataField .PivotFields("No Supporters"), "Count of No Supporters", xlCount
With .PivotFields("Count of No Supporters")
.Caption = "Sum of No Supporters"
.Function = xlSum
End With
.Name = "RSPN"
End With
End Sub

 

[Andrew Poulsom]

I thought you said your code was CommandButton code? Or are you using a button from the Forms Toolbar and assigning it that macro?

What are the headings in row 1 of your data worksheet? Is the CurrentRegion what you would expect?

 

[Mikeykt]

You're right I'm using a button from the Form Control and not the Command Button.

Can I still assign the macro to the Form Control button or do i have to add more code?

 

[Andrew Poulsom]

I don't have Excel 2007 where I am at the moment so I can't test until I get home. Are you sure that you have valid headings in row 1?

 

[Mikeykt]

Yes,

The macro works when I'm on the 'Inactive RG by Recruitment Type 2' worksheet or the 'RSPIVOT' worksheet, but not any other.

So the 1st line rows have to be correct.

Is there anything in the code that would only allow it to work on these two worksheets only?

 

[Andrew Poulsom]

The error "The pivot field name is not valid" indicates that you have a blank in the header.

 

[Andrew Poulsom]

This worked for me when assigned to a CommandButton from the Forms Toolbar on a different worksheet:

Sub RSPivotNum()
'
' RSPivotNum Macro
'
'
    Dim wksPivot As Worksheet
    Dim wksData As Worksheet
    Dim pc As PivotCache
    Dim PT As PivotTable
    Set wksPivot = Sheets("RSPIVOT")
    Set wksData = Sheets("Inactive RG by Recruit Type 2")
    wksPivot.UsedRange.Clear
    With wksData
        Set pc = .Parent.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
            "'" & .Name & "'!" & .Range("A1").CurrentRegion.Address(True, True, xlR1C1))
        Set PT = pc.CreatePivotTable(TableDestination:="'" & wksPivot.Name & "'!R3C1", TableName:="")
    End With
    With PT
        With .PivotFields("Behaviour")
            .Orientation = xlPageField
            .Position = 1
        End With
        With .PivotFields("Value")
            .Orientation = xlPageField
            .Position = 1
        End With
        With .PivotFields("Recency")
            .Orientation = xlPageField
            .Position = 1
        End With
        With .PivotFields("Segment")
            .Orientation = xlRowField
            .Position = 1
        End With
        With .PivotFields("Recruitment Summary")
            .Orientation = xlColumnField
            .Position = 1
        End With
        With .PivotFields("Recruitment Source")
            .Orientation = xlColumnField
            .Position = 2
        End With
        .AddDataField .PivotFields("No Supporters"), "Count of No Supporters", xlCount
        With .PivotFields("Count of No Supporters")
            .Caption = "Sum of No Supporters"
            .Function = xlSum
        End With
        .Name = "RSPN"
    End With
End Sub

Note that I changed the first 2 arguments of the Address property from 0 to True.

 

[Mikeykt]

That's it Andrew, that is where I was going wrong.

Many thanks