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 can't reproduce your error in Excel 2003. Is the worksheet protected?

 

[Mikeykt]

Sorry I'm using Excel 2007, is this the problem?

 

[Andrew Poulsom]

Does this work?

Set PT = pc.CreatePivotTable(TableDestination:=wksPivot.Name & "!R3C1", TableName:="")

 

[taurean]

Were you testing this code repetitively?
Delete the data (in the pivot range Sheets("RSPIVOT"))
Save the workbook and close excel and then re-run your code.

 

[Mikeykt]

Hi Andrew

The code works fine when I'm on the worksheet, yet when I try and use it when creating a command button on a seperate worksheet it comes back with a Runtime Error 1004.

It doesn't make sense to me, the code should work with a command button function correct?

Thanks

 

[Andrew Poulsom]

Did the code I posted work?

 

[Mikeykt]

Yes,

But only when I'm on that worksheet.

I'm trying to connect this macro to a command button on a seperate worksheet, and when I do that then it no longer works.

Is there a reason you can see that makes this happen?

I can't see why it won't work with a command button on a seperate worksheet.

 

[Andrew Poulsom]

What's your code now (the entire procedure)? What error do you get when it fails (number and description)?

 

[Mikeykt]

Hi

It fails at :
Set PT = pc.CreatePivotTable(TableDestination:=wksPivot.Name & "!R3C1", TableName:="")

Runtime Error 1004 - The pivot field name is not valid


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(0, 0, 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