Invalid procedure call or argument Error "5"

[firebolt]

I encounter the error message "invalid procedure or argument" error 5. What does mean?

I try to ask excel to create a pivot table on the Active Sheet. It goes to a new sheet, but it does not create a pivot table. It prompts me for Error 5.

Could you please help to resolve this ?

Below is the VBA CODE:

Sub AutoPivtbl()
Dim lastrow As Long, LastCol As Integer, i As Long, iStart As Long, iEnd As Long
Dim Newsheet As Worksheet
Application.ScreenUpdating = False
With ActiveSheet
lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
LastCol = .Cells(1, Columns.Count).End(xlToLeft).Column
.Range(.Cells(1, 1), Cells(lastrow, LastCol)).Select
Set Newsheet = Sheets.Add(Type:=xlWorksheet)
For i = 1 To Sheets.Count
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"2!R1C1:R3C8", Version:=xlPivotTableVersion12).CreatePivotTable _
TableDestination:="sheet(Newsheet)!R3C1", TableName:="PivotTable.count", DefaultVersion _
:=xlPivotTableVersion12
Next i

 

[Andrew Poulsom]

Try (snip):

TableDestination:=Newsheet.Name & "!R3C1"

 

[firebolt]

It works!!! Thanks so much. I was trying different syntax this morning....Thanks a bunch...