Error when adding pivot table: "PivotTable field name is not valid. ..."

Anniemack2

New Member
Joined
Mar 7, 2019
Messages
3
Sheesh, I've liberally borrowed code from this website already, but am still getting an error when I go to insert pivot table. What am I missing?? Would appreciate any help!

Code:
    ws_NewBP.Activate    ' Activate worksheet

    Range("A2").Select
    lastrow = ws_NewBP.Cells.Find("", , xlFormulas, xlPart, xlByRows, xlPrevious).Row - 1   ' find last row of data
    ptrange = ws_NewBP.Name & "!R1C1:R" & lastrow & "C6"  ' format = "Sheetname!R1C1:R##C6"
    
    Sheets.Add
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=ptrange, Version:=6).CreatePivotTable _
    TableDestination:="Sheet3!R3C1", TableName:="PivotTable3", DefaultVersion:=6

Ann
 

Some videos you may like

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

Anniemack2

New Member
Joined
Mar 7, 2019
Messages
3
I wanted to add that when I do "Insert pivot table" from excel (not in the macro) with the same data in the very same file, it works beautifully. But the VBA code doesn't work. I also tried recording the steps, the only difference in the recorded code is the range specification. But the range that gets computed looks fine. :confused:

Please help!
 

Anniemack2

New Member
Joined
Mar 7, 2019
Messages
3
I found my issue - in typing my last response it became more clear that it HAD to be a problem with the Range. I was including a header row that I shouldn't have been. Problem resolved. Hurray!

I'd delete this post but I don't know how to.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,351
Messages
5,528,194
Members
409,807
Latest member
nicky736

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top