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
 

Perksy_no1

Well-known Member
Joined
Oct 27, 2011
Messages
598
Office Version
365
Platform
Windows
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
 

texasaswang

New Member
Joined
Mar 13, 2011
Messages
43
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
 

chileverde

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

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

Well-known Member
Joined
Oct 27, 2011
Messages
598
Office Version
365
Platform
Windows
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
 

chileverde

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

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

Well-known Member
Joined
Oct 27, 2011
Messages
598
Office Version
365
Platform
Windows
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
 

Forum statistics

Threads
1,082,334
Messages
5,364,677
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