Amending VB variable Pivot table size

[hblbs]

Hello all

I have recorded the macro below to create a Pivot table, however I would like to use this again without having to record it. The problem I have is the the Pivot table report can be a variable length, sometime 10 rows or sometimes hundreds of row.

Below is the code, the second set of code is what I would like it to do but keep getting error messages.

    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
        "'Completed'!R1C1:R311C8").CreatePivotTable TableDestination:="", _
        TableName:="PivotTable2", DefaultVersion:=xlPivotTableVersion10

    LastRow = ActiveSheet.UsedRange.Rows.Count
    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
        "'Work Outstanding'!A1:H & LastRow").CreatePivotTable TableDestination:="", _
        TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion10

Any help is appreciated.

Thanks

 

[Ruddles]

I think this is the problem:-

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
        "'Work Outstanding'!A1:H[COLOR=red][B]"[/B][/COLOR] & LastRow).CreatePivotTable TableDestination:="", _
        TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion10

If you encounter this sort of problem in the future, build the SourceData string in a VBA variable and then Debug.Print it just before you use it. Quite often the problem will become obvious.

In this case your SourceData range would have contained the string "& LastRow" instead of its value.

 

[hblbs]

Hello Ruddles

I get a 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.

Not sure why this is, but below is what I am running.

    LastRow = ActiveSheet.UsedRange.Rows.Count
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
        "'Work Outstanding'!A1:H" & LastRow).CreatePivotTable TableDestination:="", _
        TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion10
    ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
    ActiveSheet.Cells(3, 1).Select
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Area Outstanding")
        .Orientation = xlRowField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields("Items Outstanding"), "Sum of Items Outstanding", _
        xlSum

Thanks

 

[RoryA]

FWIW, I would probably use:

   Dim strData           As String
   Dim strSheet          As String
   
   strSheet = "Work Outstanding"
   strData = "'" & strSheet & "'!" & Sheets(strSheet).Range("A1").CurrentRegion.Address(ReferenceStyle:=xlR1C1)
   
   ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=strData).CreatePivotTable TableDestination:="", _
                                                   TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion10

 

[hblbs]

Brilliant rorya, this works as required.

Thank you both for your help and input.