0

I am beyond frustrated with this, and desperately need help. I have used this code before in other workbook with no problem. Tried to copy it over, replacing the variables and workbook-specific information for the new file, only to run into this stupid error (on the line containing 'Set objPT = ...'). I even tried copying code directly out of an Excel VBA textbook for creating a pivot table and still ran into the same stupid error. I cannot figure out what I am doing wrong.

Sub SetupPivotTable()

    Dim PTWS As Worksheet
    Dim objPT As PivotTable
    Dim objPC As PivotCache

    Range("Q3").Activate
    Selection.End(xlDown).Select
    ActiveCell.Offset(1, 0).Select
    bottomRow = Selection.Address

'    MsgBox bottomRow

    Range("A3", bottomRow).Select
    Selection.Name = "UTS_Data"

    Sheets.Add After:=Worksheets(Worksheets.Count)
    Set PTWS = ActiveSheet
    ActiveSheet.Name = "Graph"
    Sheet1.Activate

    Set objPC = ActiveWorkbook.PivotCaches.Create _
        (xlDatabase, "UTS_Data")
    Set objPT = objPC.CreatePivotTable(PTWS.Range("a3"), _
        TableName:="UTS_PT")

End Sub

Alternatively, I tried using this:

    Set objPT = objPC.CreatePivotTable _
        TableDestination:="'Graph'!R3C1", TableName:="UTS_PT"

And it instead errored out with 'Expected End of Statement' on 'TableDestination'

There is more to this macro, but this is all that's relevant for the current problem.

dwirony
  • 5,487
  • 3
  • 21
  • 43
doleenovodno
  • 1
  • 1
  • It's likely that the data in `"UTS_Data"` could be the culprit here. The data needs to be in a regular looking table with concurrent data throughout all the columns in your range. Also, you don't need `TableName:=` in your Create line. `Set objPT = objPC.CreatePivotTable(PTWS.Range("a3"), "UTS_PT")` should be sufficient. What does your data look like? – Busse Jun 15 '18 at 14:58
  • I'd also like to encourage you read up on [this guide (CLICK ME!)](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) to understand how using `Select` and `Activate` in your code can be detrimental. It can often cause unexpected errors, slows down processes, and is overall frowned upon. It's a great starting point to better learn how to fine-tune your code! – Busse Jun 15 '18 at 15:03
  • Thank you for your reply. Unfortunately, removing `TableName:=` did not resolve the problem. I would be happy to provide images of what the data looks like, but am unsure how to do that. – doleenovodno Jun 15 '18 at 15:14

0 Answers0