Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro
Hi,
I have set up a macro which picks up some data from an external database, prompts user for citeria and then presents the data on a worksheet. It then creates a Pivot Table using the data presented. However, as the user can select different types of critera (ie. Date range) the number of records can be different each time. Therefore the data for the pivot table increases or decreases. How can I code this to incorporate the discrepancy? Currently I have this.... ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _ "'Raw Data'!R1C1:R9166C5").CreatePivotTable TableDestination:="", TableName _ :="PivotTable1" The "R1C1:R9166C5" means that only records up to Cell 9166 are read. The rest are ignored and not included in the Pivot Table. Can any one help? Many thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro
I have a few sheets with pivottables based on a dynamic
named range. Insert a named range called "MyRange" with the following equation: =Offset('Raw Data'!A1,0,0,counta('Raw Data'!$A:$A),5) Then change the relevant lines to: Sourcedata:= "'Raw Data'!MyRange" That might help. Pete -----Original Message----- Hi, I have set up a macro which picks up some data from an external database, prompts user for citeria and then presents the data on a worksheet. It then creates a Pivot Table using the data presented. However, as the user can select different types of critera (ie. Date range) the number of records can be different each time. Therefore the data for the pivot table increases or decreases. How can I code this to incorporate the discrepancy? Currently I have this.... ActiveWorkbook.PivotCaches.Add(SourceType:=xlData base, SourceData:= _ "'Raw Data'!R1C1:R9166C5").CreatePivotTable TableDestination:="", TableName _ :="PivotTable1" The "R1C1:R9166C5" means that only records up to Cell 9166 are read. The rest are ignored and not included in the Pivot Table. Can any one help? Many thanks . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro recorded... tabs & file names changed, macro hangs | Excel Worksheet Functions | |||
Macro Help Needed - Excel 2007 - Print Macro with Auto Sort | Excel Worksheet Functions | |||
My excel macro recorder no longer shows up when recording macro | Excel Discussion (Misc queries) | |||
macro to delete entire rows when column A is blank ...a quick macro | Excel Programming | |||
Start Macro / Stop Macro / Restart Macro | Excel Programming |