I've a macro in order to create a pivot table from fresh new data every day.
It start's by formating the information that comes from an external source,
and then creates the pivot table.
I can't set the code in order to manage to create a pivot table that
includes all information and that doesn't show me any blank cells/values.

The pivot code ids this:
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDa tabase, SourceData:= _
"Sheet1!R1C1:R313C9",
Version:=xlPivotTableVersion12).CreatePivotTable _
TableDestination:="Sheet4!R3C1", TableName:="PivotTable1",
DefaultVersion _
:=xlPivotTableVersion12

How can I do in order to change the source data automatically?

Hummm, I just answered a VERY similar question yesterday. As I mentioned on
the other post...


Ah! Great question! It took me a while to figure out how to do this. Now,
I can share the answer with you.

Recorded Macro with Absolute Reference: Sheet1!R1C1:R30C10
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
"Sheet1!R1C1:R30C10").CreatePivotTable TableDestination:="",
TableName:= _
"PivotTable1", DefaultVersion:=xlPivotTableVersion10

Recorded Macro with Absolute Reference changed to Relative Reference:
Sheets("Sheet1").Range("A1").CurrentRegion).
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
Sheets("Sheet1").Range("A1").CurrentRegion).Create PivotTable _
TableDestination:=Sheets("Sheet2").Range("A3"),
TableName:="PivotTable1", _
DefaultVersion:=xlPivotTableVersion10


The second example will extend the Pivot Table range to the entire Pivot
Table, no matter whether the Pivot Table source data grows or shrinks over
time.


Regards,
Ryan---

--
RyGuy


"Pjvicente" wrote:

I've a macro in order to create a pivot table from fresh new data every day.
It start's by formating the information that comes from an external source,
and then creates the pivot table.
I can't set the code in order to manage to create a pivot table that
includes all information and that doesn't show me any blank cells/values.

The pivot code ids this:
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDa tabase, SourceData:= _
"Sheet1!R1C1:R313C9",
Version:=xlPivotTableVersion12).CreatePivotTable _
TableDestination:="Sheet4!R3C1", TableName:="PivotTable1",
DefaultVersion _
:=xlPivotTableVersion12

How can I do in order to change the source data automatically?

Thanks, Ryan.

It worked just fine.

Best regards
PJ

"ryguy7272" escreveu:

Hummm, I just answered a VERY similar question yesterday. As I mentioned on
the other post...


Ah! Great question! It took me a while to figure out how to do this. Now,
I can share the answer with you.

Recorded Macro with Absolute Reference: Sheet1!R1C1:R30C10
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
"Sheet1!R1C1:R30C10").CreatePivotTable TableDestination:="",
TableName:= _
"PivotTable1", DefaultVersion:=xlPivotTableVersion10

Recorded Macro with Absolute Reference changed to Relative Reference:
Sheets("Sheet1").Range("A1").CurrentRegion).
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
Sheets("Sheet1").Range("A1").CurrentRegion).Create PivotTable _
TableDestination:=Sheets("Sheet2").Range("A3"),
TableName:="PivotTable1", _
DefaultVersion:=xlPivotTableVersion10


The second example will extend the Pivot Table range to the entire Pivot
Table, no matter whether the Pivot Table source data grows or shrinks over
time.


Regards,
Ryan---

--
RyGuy


"Pjvicente" wrote:

I've a macro in order to create a pivot table from fresh new data every day.
It start's by formating the information that comes from an external source,
and then creates the pivot table.
I can't set the code in order to manage to create a pivot table that
includes all information and that doesn't show me any blank cells/values.

The pivot code ids this:
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDa tabase, SourceData:= _
"Sheet1!R1C1:R313C9",
Version:=xlPivotTableVersion12).CreatePivotTable _
TableDestination:="Sheet4!R3C1", TableName:="PivotTable1",
DefaultVersion _
:=xlPivotTableVersion12

How can I do in order to change the source data automatically?