VBA Macro Creating a Pivot Table on a new sheet

Lres81715

Board Regular
Joined
Aug 26, 2015
Messages
147
Good morning all,

So I'm running into Run-time error '1004':
The PivotTable field name is not valid. To Create a PivotTable report,
you must use data that is organized as a list with labeled columns, If
you are changing the name of a PivotTable field, you must type a new
name for the field.

My data is good, has headers and I've used this code on another project and it worked great there.

Code:
Sub Macro1()
    Dim sh1         As Worksheet 'Detail-Orders Master Worksheet    Dim ws          As Worksheet 'Pivot Table new Worksheet
    Dim datastart   As Range 'For PivotTable creation
    Dim datarange   As Range 'For PivotTable creation
    Dim ptrange     As String 'For PivotTable creation

    Set sh1 = Sheets("Detail-Orders")

    Set datastart = sh1.Range("A1")
    Set datarange = sh1.Range(datastart, datastart.SpecialCells(xlLastCell)) 
    ptrange = sh1.Name & "!" & datarange.Address(ReferenceStyle:=x1R1C1) ' format = "Sheetname!R#C#:R##C##"

    Set ws = Sheets.Add
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        ptrange, Version:=xlPivotTableVersion10).CreatePivotTable _
        TableDestination:=ws.Cells(3, 1), TableName:="PivotTable1", DefaultVersion _
        :=xlPivotTableVersion10 ' ptrange = "Detail-Orders!R1C1:R1351C23"

End Sub
I've swapped out Version:=xlPivotTableVersion10 with Version:=xlPivotTableVersion15, No luck. Changed ptrange to Detail-Orders!R1C1:R1351C23 as the hardcoded number and I'm still getting the error. Any assistance in steering me in the right direction would be appreciated.

Thanks!
 

Lres81715

Board Regular
Joined
Aug 26, 2015
Messages
147
Ok, well I ended up finding the problem. There were formatted columns after column 23 with no headers. Columns X-AA, while blank were being added during this phase.
Code:
Set datarange = sh1.Range(datastart, datastart.SpecialCells(xlLastCell))
I've patched it with this. It's crude but does the trick and that's all that matters at this point.

Code:
Sub Macro2()    Dim sh1         As Worksheet 'Detail-Orders Master Worksheet
    Dim ws          As Worksheet 'Pivot Table new Worksheet
    Dim ptrange     As String 'For PivotTable creation
    Dim LastRow     As Long
    Dim LastCol     As Long


    Set sh1 = Sheets("Detail-Orders")


    LastRow = sh1.Cells.Find("*", , xlFormulas, xlPart, xlByRows, xlPrevious).Row
    LastCol = sh1.Cells.Find("*", , xlFormulas, xlPart, xlByColumns, xlPrevious).Column


    ptrange = sh1.Name & "!R1C1:R" & LastRow & "C" & LastCol ' format = "Sheetname!R#C#:R##C##"


    Set ws = Sheets.Add
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        ptrange, Version:=xlPivotTableVersion10).CreatePivotTable _
        TableDestination:=ws.Cells(3, 1), TableName:="PivotTable1", DefaultVersion _
        :=xlPivotTableVersion10 ' ptrange = "Detail-Orders!R1C1:R1351C23"


End Sub
Thank you MrExcel forums for the LastRow and LastCol suggestions.
I'll attempt to clean it up later for future use.
 

gurkan

New Member
Joined
Dec 20, 2016
Messages
6
thanks for that, I think easiest way to create PT in new sheet is to record a macro and then modify the code with dynamic statements.
 

Forum statistics

Threads
1,082,333
Messages
5,364,675
Members
400,810
Latest member
elbashka

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top