Error 1004

[vmjan02]

I have mention code and it creates pivot table, but concern is it gives error as 1004, I am using excel 2016. Any Idea.

Code:-
Private Sub CommandButton1_Click()
'Sheets("AG1 Node Down").Select
Dim Pt As PivotTable
Dim PtCache As PivotCache
Dim pageField1 As String
Dim pageField2 As String
Dim pageField3 As String
Dim rowField1 As String
Dim rowField2 As String
Dim colField As String
Dim dataField As String


On Error Resume Next
Application.DisplayAlerts = False
Sheets("Repeat Cnt SAP ID WK33").Delete
On Error GoTo 0
Application.DisplayAlerts = True


Set s = Sheets("Sheet2")
With Worksheets.Add
.Name = "Repeat Cnt SAP ID WK33"
End With


pageField1 = s.Cells(1, 2).Value
rowField1 = s.Cells(1, 3).Value


colField1 = s.Cells(1, 10).Value
dataField = s.Cells(1, 11).Value


Worksheets("AG1 Node Down").Activate
ActiveSheet.Range("C5").Select
ActiveSheet.Range(Selection, Selection.End(xlToRight)).Select
ActiveSheet.Range(Selection, Selection.End(xlDown)).Select
Selection.Name = "Vir"

' Error here 1004--- it is not creating pivot table '
Set PtCache = ActiveWorkbook.PivotCaches.Add( _
SourceType:=xlDatabase, _
SourceData:=s.Range("Vir"))


Set Pt = PtCache.CreatePivotTable( _
TableDestination:=Sheets("Repeat Cnt SAP ID WK33").Range("A3"), _
TableName:="Q1")




End Sub

 

[Trebor76]

Any Idea.

At a guess I'd say there's something wrong with the Vir named range. Check it exists and that it's been set properly via Name Manger (accessed by pressing Ctrl + F3 i.e. press and hold Ctrl and then press F3).

Robert

 

[vmjan02]

Yes "Vir" name range is properly set in Name Manager.

 

[Trebor76]

Not sure then as it worked for me
Make sure Sheet2 is not protected is something else you can try.

 

[Trebor76]

Actually when you set the "Vir" named range it's based on the "AG1 Node Down" tab yet in your code you're trying to associate the "Vir" named range with "Sheet2" i.e. s.Range("Vir")) which is causing the issue. Remove the "s." from this line of code that's failing...

Set PtCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=s.Range("Vir"))

...so it's like this and all should be good:

Set PtCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=Range("Vir"))

Robert