macro creating new pivot table
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? |
macro creating new pivot table
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? |
macro creating new pivot table
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? |
All times are GMT +1. The time now is 03:08 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com