Code for Dynamic Pivot Table Range

[weefisheads]

I'm trying to write a macro that will take some data, clean it up, and then convert the data into a pivot table. Alas, I can't seem to come up with a way to make it so that the source data for the pivot table is dynamic.

As I'm pretty weak at writing code outright, I usually record what I want to do and work from there. By doing this, I end up with code that look like this -

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"'Data.rdl'!R1C1:R43C6").CreatePivotTable TableDestination:="", _
TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion10

This is fine and dandy if the data range is A1:F43, but though the data is always 6 colums wide, it can be anywhere between 10 and 10000 rows long.

I've tried using

Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select

but I can't seem to get around naming an absolute range for the SourceDate:=

Any suggestions? I'm sure it's something quite obvious, but the syntax eludes me.

Thanks,

dB

 

[Tom Urtis]

See if the CurrentRegion property gets you headed in the right direction:

ThisWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=Worksheets("Data.rdl").Range("A1").CurrentRegion).CreatePivotTable TableDestination:=Range("A3")

 

[weefisheads]

Incredible - many thanks, Tom!

dB

 

[VBABEGINER]

Hi,

Similar to this, I want to create dynamic range for Pivot. But this is not working.. Could you pls help out

ActiveSheet.PivotTables("PivotTable2").ChangePivotCache ActiveWorkbook.PivotCaches.Create _
(SourceType:=xlDatabase, SourceData:=Worksheets("Details").Range("A1").CurrentRegion).CreatePivotTable, Version:=xlPivotTableVersion14

See if the CurrentRegion property gets you headed in the right direction:

ThisWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=Worksheets("Data.rdl").Range("A1").CurrentRegion).CreatePivotTable TableDestination:=Range("A3")

 

[Tom Urtis]

Can you explain what you are doing? Why the ChangePivotCache method? Are you trying to create a pivot table from a pivot table? Or do you just want to create a new pivot table from a range of data that (as far as this new pivot table would be concerned) is coincidentally used for the first pivot table? A bit more context would be useful for what you want to do, and why you want to do it, and how a previous and in-place pivot table has whatever bearing it might have on the new cache you want to create.

 

[VBABEGINER]

Hello, First of all Thank You for your kind response.

workbook = Analysis.xlsx
1st sheet = DTL
2nd sheet = TRS

On my 2nd sheet (Sheet name is TRS), I want to create a Pivot. I am taking only one field in the Row label and the same field i am taking in Values for their individual Sum.

In my 1st sheet, (Sheet name is DTL), once my data copied from another source the pivot creation will my 2nd step. Therefore, I am said "dynamic range". B'coz 1st Sheet data may less or more depends on the raw data.

So this my code,

Sheets("TRS").Select
Range("B4").Select

ActiveSheet.PivotTables("PivotTable2").ChangePivotCache ActiveWorkbook. _
PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"\\serverpath\[ANALYSIS.xlsx]DTS!R1C1:R3C32" _
, Version:=xlPivotTableVersion14)

Can you explain what you are doing? Why the ChangePivotCache method? Are you trying to create a pivot table from a pivot table? Or do you just want to create a new pivot table from a range of data that (as far as this new pivot table would be concerned) is coincidentally used for the first pivot table? A bit more context would be useful for what you want to do, and why you want to do it, and how a previous and in-place pivot table has whatever bearing it might have on the new cache you want to create.

 

[VBABEGINER]

Any Help on this...

 

[Tom Urtis]

I did not understand everything you wrote in your reply yesterday. Please re-read your two sentence lines, especially the second one.

But first, by any chance, are things not working for you because you are trying to execute VBA code in a .xlsx extension workbook as you said that it is, which (unless your code is in an add-in of a .xlam extension) would not execute programming code.

 

[VBABEGINER]

Tom, Thanks for reply and concern..May b im not able to convey the msg correctly.

Ok, Can atleast help me...In sheet1 whatever be the data, for that data assign name and use this name into pivot for range....Please..Hope this helps...

I did not understand everything you wrote in your reply yesterday. Please re-read your two sentence lines, especially the second one.

But first, by any chance, are things not working for you because you are trying to execute VBA code in a .xlsx extension workbook as you said that it is, which (unless your code is in an add-in of a .xlam extension) would not execute programming code.