VBA Macro Creating a Pivot Table on a new sheet

[Lres81715]

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.

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]

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.

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.

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]

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.