TableDestination:=“Sheet2!R1C1”

Sub Macro1()

Sheet2.Select
ActiveWindow.SmallScroll Down:=-69
Cells.Select
Selection.Delete Shift:=xlUp
Range("A1").Select
Dim Endrow&
Endrow = Sheet3.Range("H1").End(xlDown).Row
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
Sheet3.Name & "!R1C8:R" & Endrow & "C21", Version:=6).CreatePivotTable _
**TableDestination:=Sheets("Sheet2").Cells(1, 1)**, TableName:= _
"PivotTable1", DefaultVersion:=6


I tried to make this Pivot Tables Macro insert into Sheet2!R1C1 (which is an existing worksheet), instead of creating a new worksheet. However, I receive Run-time error

5: Invalid Procedure call or argument.

I have tried to following methods

TableDestination:=Sheets("Sheet2").Cells(1, 1)
TableDestination:="Sheet2!R1C1"
TableDestination:=Sheet2.name & "!R1C1"


but all failed. How can I make this right?

2 Answers
2

Something like

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
Sheet3.Name & "!R1C8:R" & Endrow & "C21", Version:=6).CreatePivotTable TableDestination:=Sheet2.[A1], TableName:="PivotTable1", DefaultVersion:=6


Your code works on the assumption that there are worksheets with the Code Name (not names!) of Sheet2 and Sheet3. Could it be that you're referring to the sheet's object name which possibly has changed? This works … if you have a Sheet2 and Sheet3

Sub Macro1()
Sheet2.Select
ActiveWindow.SmallScroll Down:=-69
Cells.Select
Selection.Delete Shift:=xlUp
Range("A1").Select
Dim Endrow&
Endrow = Sheet3.Range("H1").End(xlDown).Row
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
Sheet3.Name & "!R1C8:R" & Endrow & "C21", Version:=6).CreatePivotTable _
TableDestination:=Sheets("Sheet2").Cells(1, 1), TableName:= _
"PivotTable1", DefaultVersion:=6
End Sub


Perhaps check that when you refer to Sheet2 and Sheet3 that they still exists.

Public Sub ListSheetNames()
Dim vSheet As Worksheet
For Each vSheet In Worksheets
Debug.Print "Code Name/Sheet Name: " & vSheet.CodeName & " / " & vSheet.Name
Next
End Sub


Maybe change Sheet2 reference to the below … and similarly for Sheet3

sheets("Sheet2")


By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.