Solved: Dynamic Pivot Table contents

[justdream]

Dears,

I’d like to create macro, do some calculations and charts using pivot table..
My concern is to design macro selecting all the data in the sheet to be used for Pivot analysis
Whatever the number of existing columns
I’m already using below code:

[vba]Cells.Select
Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Sheet1!R1C1:R1048576C151", Version:=xlPivotTableVersion14).CreatePivotTable _
TableDestination:="Sheet4!R1C1", TableName:="PivotTable1", DefaultVersion _
:=xlPivotTableVersion14[/vba]
But I;ve tested it after deleting few columns from my inout file and it has give me Debug error message
Kindly advice how to sort this issue out

[Paul_Hossler]

The macro recorder hard coded the SourceData address, and when you deleted columns without correcting the address, you'll get the error

[VBA]
Sub Macro1()
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
SourceData:=ActiveWorkbook.Worksheets("Sheet1").Cells(1, 1).CurrentRegion, _
Version:=xlPivotTableVersion14).CreatePivotTable _
TableDestination:=ActiveSheet.Cells(3, 1), _
TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion14
End Sub
[/VBA]

I try to let Excel figure out where the data is

Paul

[Capungo]

Hello JustDream,

I have had the same problem a while ago. I have found some solutions in Ozgrid Forum. Since then I have no problem. I did it all as follows:

1. You should create a Dynamic Named Range either by recording that macro to use it later as part of your future code or not doing it. Doesn't matter. An example dynamic range formula is shown below.

[ExampleRange]: =OFFSET($A$1,0,0,COUNTA($A:$A),COUNTA($1:$1))

2. Then, go to the Pivot Table menu and when you get the Pivot Table Dialog Box, instead of selecting a specific range on the current sheet, you should press F3 and select previously created dynamic range [ExampleRange]

3. Create your pivot table.

4. Add some extra data to your data sheet and control if the pivot table includes those data...

5. You can record the macro while doing all steps above to get the code...