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

chileverde

New Member
Joined
May 18, 2012
Messages
5
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
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
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

Rich (BB code):
            ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
                Sheets("list by brand").Range("A1").CurrentRegion).CreatePivotTable TableDestination:= _
                "", TableName:="InvPivot", DefaultVersion:= _
                xlPivotTableVersion10
 
Upvote 0
Wouldn't this work, straight from the Macro Recorder:

Code:
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.

Rich (BB code):
    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
            Sheets("list by brand").Range("A1").CurrentRegion).CreatePivotTable TableDestination:= _
            "", TableName:="InvPivot", DefaultVersion:= _
            xlPivotTableVersion10
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,027
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top