How do I base pivot table on all active cells in a Macro?

[chileverde]

Can anyone tell me how to "select all" cells in a macro for a pivot table?

I'm trying to create a macro to build a pivot table based on all the populated cells in a worksheet (Excel 2007), the problem being that dataset will not always be the same size.

I originally built this using the macro recorder, but when I hit "Ctrl-A", the macro actually recorded a defined range of cells. Obviously this won't work when the size of the dataset changes. How can I tell the PivotCaches.Create method to use everything from R1C1 to the last cell for the SourceData (i.e., do the same thing as Ctrl-A)? The bit red is what I need to fix:

ActiveWorkbook.PivotCaches.Create(SourceType:=xlConsolidation, SourceData:= _
Array("Sheet1!R1C1:R16C81"), Version:=xlPivotTableVersion12). _
CreatePivotTable TableDestination:="", TableName:="PivotTable3", _
DefaultVersion:=xlPivotTableVersion12

Thanks in advance,
David

 

[Perksy_no1]

Hi David,

Im using excel 2002 but I found some code on here which I use when creating pivot tables in vb with different ranges that may help you

            ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
                Sheets("list by brand").Range("A1").CurrentRegion).CreatePivotTable TableDestination:= _
                "", TableName:="InvPivot", DefaultVersion:= _
                xlPivotTableVersion10

 

[texasaswang]

Wouldn't this work, straight from the Macro Recorder:

Sub Create_Pivot()
    Cells.Select
    Sheets.Add
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Sheet1!R1C1:R1048576C6", Version:=xlPivotTableVersion12).CreatePivotTable _
        TableDestination:="Sheet4!R3C1", TableName:="PivotTable3", DefaultVersion _
        :=xlPivotTableVersion12
    Sheets("Sheet4").Select
    Cells(3, 1).Select
End Sub

 

[chileverde]

Perksy,

doesn't this just base the pivot on the current region selected? I can't select the region prior to running the macro, as this bit is in the middle of the macro after some other processing. I'm trying to get the macro to select the range for me, which will vary in size. I essentially want it to duplicate what one would get by hitting Ctrl-A.

Thanks,
David

 

[chileverde]

Texasaswang,

I can't do it quite like that with R1048576. I have 81 columns and trying to pivot on that many rows just takes up way too much memory (tried it with 100,000 and it froze). I can do it with 1000 however, and it will work. Hopefully won't ever have more than 1000 rows.

The problem with this is that it creates a bunch of blank rows in my final table -- what I'm after isn't actually the pivot table, but rather the the table I get when double-clicking on Count of Values grand total (I'm using this macro to automate data normalization). By selecting more rows than I actually need, it creates a bunch of extra rows in my final table. It's not a very elegant solution, but I guess I can filter these extra rows out afterwords to make it work.

Thanks,
David

 

[Perksy_no1]

Perksy,

doesn't this just base the pivot on the current region selected? I can't select the region prior to running the macro, as this bit is in the middle of the macro after some other processing. I'm trying to get the macro to select the range for me, which will vary in size. I essentially want it to duplicate what one would get by hitting Ctrl-A.

Thanks,
David

Hi David,

I use this piece of code in the middle of a macro. I know where the start of the range I want to pivot (always in A1 in sheet list by brand) then the code picks up all the info from there. A bit like using Ctrl - A.

Its always worked ok for me doing it like this.

    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
            Sheets("list by brand").Range("A1").CurrentRegion).CreatePivotTable TableDestination:= _
            "", TableName:="InvPivot", DefaultVersion:= _
            xlPivotTableVersion10

 

[chileverde]

Thanks Perksy. Looking at Help, it appears what you have provided should work (and it does if I copy and past what you wrote and only change the sheet name), but when I change the SourceType to xlConsolidation, I get a "Method 'Add' of object 'PivotCaches' failed" error.

But according to Help, "" It can be a Range object (when SourceType is either xlConsolidation or xlDatabase)...", so I'm not sure what I'm doing wrong. I'll keep at it and see if I can get it to work.

Thanks again

 

[chileverde]

Got it! If I define the CurrentRegion as a named range first, and then use the named range as the SourceData it works. Thanks!


ActiveSheet.ListObjects.Add(xlSrcRange, Range("A1").CurrentRegion, , xlYes).Name _
= "myRange"
ActiveSheet.ListObjects("myRange").TableStyle = ""
ActiveWorkbook.PivotCaches.Create(SourceType:=xlConsolidation, SourceData:= _
"=myRange", Version:=xlPivotTableVersion12). _
CreatePivotTable TableDestination:="", TableName:="PivotTable3", _
DefaultVersion:=xlPivotTableVersion12

 

[Perksy_no1]

Glad to be off help! I actually stumbled across that bit of code looking for something else on this site but it I use it on all my code for pivots now