Insert excel data before the pivot table

Question

I keep getting this error: Run-time error '5':

Invalid procedure call or argument:

I am just learning VBA...& yes, I have searched for the answer on here before I tried to ask this question. First I tried to add single quotes to the TableDestination(however there's no space in the sheet name), & then I tried to refresh the table which I did, no bueno. I also made sure that the pivot table name was unique and not previously used, lastly I came across a solution to remove the Sheets.Add statement and leave TableDestination:="". This worked but when I generate the report it inserts the pivot table before the excel data, and I wanted it the other way around, I'm sure what I am doing wrong is very simple, but I can't seem to find what I need

Sub PostProcessing()
 Dim MainWorksheet As Worksheet

 Set MainWorksheet = ActiveWorkbook.Worksheets("Query1")

 Dim pt     As PivotTable
 Dim pf     As PivotField
 Dim pi     As PivotItem
 Dim pc     As PivotCache
 Dim ws     As Worksheet

 With ActiveWorkbook
    For Each pc In .PivotCaches
        pc.MissingItemsLimit = xlMissingItemsNone
    Next pc
 End With

 'Create a pivot table on page Two

 Sheets.Add After:=Sheets(Sheets.Count)

  ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
  SourceData:= "Query1!R1C7:R90C8", _
  Version:=xlPivotTableVersion14).CreatePivotTable _
  TableDestination:="Sheet1!R1C1", TableName:= "PTCtable"

 With ActiveSheet.PivotTables("PTCtable")'
    With .PivotFields("Assigned To")
       'Set the Row Field
       .Orientation = xlRowField
       .Position = 1
    End With

    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.ChartType = xlColumnClustered
 End With

End Sub

What version of Excel are you using? xlPivotTableVersion14 is for a specific Excel version. http://stackoverflow.com/questions/23115286/excel-2010-vba-not-working-in-excel-2012 — MatthewD, Aug 31 '15 at 19:25
TableDestination:="'Sheet1'!R1C1", TableName:= "PTCtable" from http://stackoverflow.com/questions/17020446/invalid-procedure-call-or-argument-when-creating-a-pivot-table — MatthewD, Aug 31 '15 at 19:29
Yeah I was able to get your code to work for that line I normally use ranges when using TableDestination so I'd look at your string names here. Also I normally do some sort of clean up before writing pivots and graphs. working_pivot_table.ClearTable working_pivot_table.PivotCache.MissingItemsLimit = xlMissingItemsNone working_pivot_table.TableRange2.Clear graph_worksheet.ChartObjects.Delete and I find those things keep most issues at bay and while you're at it put Option Explicit at the top of your subs — jamesC, Aug 31 '15 at 19:30
@MatthewD I tried the single quotes, but it didn't work. JamesC Would it be possible for you to show my a quick example? (sorry still very new to VBA). — Whitney King, Aug 31 '15 at 19:42
I've encountered this problem before and the solution was to split creating the PivotCache and creating the PivotTable into two lines. Not sure if that is the problem here but it's worth a try I think. — Kyle, Aug 31 '15 at 21:13

score 0 · Answer 1 · answered Aug 31 '15 at 21:48

Your problem has nothing to do with names of worksheet, you simply don't call a function in a right way. You want to create a Pivot table, while you are not assigning to it any name: you miss the left part of the equation. The following must work.

    pc = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase,         
    SourceData:="Query1!R1C7:R90C8", Version:=xlPivotTableVersion14)
    pt = pc.CreatePivotTable(TableDestination:="Sheet1!R1C1",TableName:="PTCtable")

if not then try:

    Set pc = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase,         
    SourceData:="Query1!R1C7:R90C8", Version:=xlPivotTableVersion14)
    Set pt = pc.CreatePivotTable(TableDestination:="Sheet1!R1C1",TableName:="PTCtable")

Neither of those worked for me..I apologize I did not mention in my original post that I tried both of those methods. I ended up taking it out..But you can see at the top of my post where I declared the names pt and pc. The error I get with that is: Run-time error '91:' Object variable or With block variable not set. & the debugger highlights the pt = pc.CreatePivotTable(TableDestination:="Sheet1!R1C1",TableName:="PTCtable"). Same exact error when they are both set — Whitney King, Sep 01 '15 at 13:53

Whitney King · Answer 2 · 2015-09-02T19:23:07.217

So I used a little of @jamesC advice and I used range for the table data instead, & that got it working. I'm sure some of the code may not be the most efficient, as I am still very new to the language but below is what I did:(Open to any suggestions for improvement)

 Dim pt          As PivotTable
 Dim pf          As PivotField
 Dim pi          As PivotItem
 Dim pc          As PivotCache

  With ActiveWorkbook
    For Each pc In .PivotCaches
        pc.MissingItemsLimit = xlMissingItemsNone
    Next pc
  End With

 'Create a pivot table on page Two

  ActiveWorkbook.Sheets.Add After:=Worksheets(Worksheets.Count)

  ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
  SourceData:="Query1!R1C7:R90C8", _
  Version:=xlPivotTableVersion14).CreatePivotTable _
  TableDestination:=Sheets(2).Range("A1:B1"), TableName:="PTCtable"


With Sheets(2).PivotTables("PTCtable") '
    With .PivotFields("Assigned To")
       'Set the Row Field
       .Orientation = xlRowField
       .Position = 1
    End With

Sheets(2).Select

ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlColumnClustered

End With

Insert excel data before the pivot table

2 Answers2