Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
dynamic source range change in pivot table
Dear Experts,
I have an existing pivot table named PivotTable2 in sheet "summary" and its source in another sheet "Data" in same workbook. "Data" always changes, The row and columne number in "Data" are two calculated results stored in variable lrow2 and lcol2. I would like to keep the layout and location of PivotTable2 in "Summary" sheet but only change the data source range in sheet "Data". I am using excel 2002 and the folowing code does not work. Sheet("Data").Activate Dim mysource2 as Range With ActiveSheet Set mysource2 = .Range(.Cells(1, 1), .Cells(lrow2, lcol2)) End With Sheets("Summary").Select ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:=mysource2 How do I change a range of an existing Pivot Table without changing anyother settings? Thanks! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
dynamic source range change in pivot table
You do not need code for this. You can use a dynamic named range. Check out
this link... http://www.contextures.com/xlPivot01.html and/or http://www.cpearson.com/excel/named.htm#Dynamic -- HTH... Jim Thomlinson "will-d" wrote: Dear Experts, I have an existing pivot table named PivotTable2 in sheet "summary" and its source in another sheet "Data" in same workbook. "Data" always changes, The row and columne number in "Data" are two calculated results stored in variable lrow2 and lcol2. I would like to keep the layout and location of PivotTable2 in "Summary" sheet but only change the data source range in sheet "Data". I am using excel 2002 and the folowing code does not work. Sheet("Data").Activate Dim mysource2 as Range With ActiveSheet Set mysource2 = .Range(.Cells(1, 1), .Cells(lrow2, lcol2)) End With Sheets("Summary").Select ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:=mysource2 How do I change a range of an existing Pivot Table without changing anyother settings? Thanks! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
dynamic source range change in pivot table
Thank you. I am not sure if I have blank cells or rows in middle of my data
will this work? Thank you Jim. "Jim Thomlinson" wrote: You do not need code for this. You can use a dynamic named range. Check out this link... http://www.contextures.com/xlPivot01.html and/or http://www.cpearson.com/excel/named.htm#Dynamic -- HTH... Jim Thomlinson "will-d" wrote: Dear Experts, I have an existing pivot table named PivotTable2 in sheet "summary" and its source in another sheet "Data" in same workbook. "Data" always changes, The row and columne number in "Data" are two calculated results stored in variable lrow2 and lcol2. I would like to keep the layout and location of PivotTable2 in "Summary" sheet but only change the data source range in sheet "Data". I am using excel 2002 and the folowing code does not work. Sheet("Data").Activate Dim mysource2 as Range With ActiveSheet Set mysource2 = .Range(.Cells(1, 1), .Cells(lrow2, lcol2)) End With Sheets("Summary").Select ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:=mysource2 How do I change a range of an existing Pivot Table without changing anyother settings? Thanks! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
dynamic source range change in pivot table
I got an error, because when I click a button, my program clear the contents
in "Data" sheet except the title first, then fill the empty field with new data. Using the build-in dynamic range, the range was decided before the program filling the new data so only first row is defined, then some filed changed on "Summary" sheet and the fake report occupies other space I need to clear but you can not delete part of a pivot table then I got the error. Is there a way to control the time or sequence of this build-in function so that it can change the range after the old data are cleared and new data are filled? Thanks. Will "Jim Thomlinson" wrote: You do not need code for this. You can use a dynamic named range. Check out this link... http://www.contextures.com/xlPivot01.html and/or http://www.cpearson.com/excel/named.htm#Dynamic -- HTH... Jim Thomlinson "will-d" wrote: Dear Experts, I have an existing pivot table named PivotTable2 in sheet "summary" and its source in another sheet "Data" in same workbook. "Data" always changes, The row and columne number in "Data" are two calculated results stored in variable lrow2 and lcol2. I would like to keep the layout and location of PivotTable2 in "Summary" sheet but only change the data source range in sheet "Data". I am using excel 2002 and the folowing code does not work. Sheet("Data").Activate Dim mysource2 as Range With ActiveSheet Set mysource2 = .Range(.Cells(1, 1), .Cells(lrow2, lcol2)) End With Sheets("Summary").Select ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:=mysource2 How do I change a range of an existing Pivot Table without changing anyother settings? Thanks! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
dynamic source range change in pivot table
You have completely lost me... Where is your data coming from.
If it is coming from an external database why not just hook the pivot table directly to the database? If you are impoorting it from other workbooks then do your import and when the is complete use code something like sheets("Sheet1").pivottables(1).Refreshtable Otherwise I am not to sure exactly what the issue is... More details will be required. -- HTH... Jim Thomlinson "will-d" wrote: I got an error, because when I click a button, my program clear the contents in "Data" sheet except the title first, then fill the empty field with new data. Using the build-in dynamic range, the range was decided before the program filling the new data so only first row is defined, then some filed changed on "Summary" sheet and the fake report occupies other space I need to clear but you can not delete part of a pivot table then I got the error. Is there a way to control the time or sequence of this build-in function so that it can change the range after the old data are cleared and new data are filled? Thanks. Will "Jim Thomlinson" wrote: You do not need code for this. You can use a dynamic named range. Check out this link... http://www.contextures.com/xlPivot01.html and/or http://www.cpearson.com/excel/named.htm#Dynamic -- HTH... Jim Thomlinson "will-d" wrote: Dear Experts, I have an existing pivot table named PivotTable2 in sheet "summary" and its source in another sheet "Data" in same workbook. "Data" always changes, The row and columne number in "Data" are two calculated results stored in variable lrow2 and lcol2. I would like to keep the layout and location of PivotTable2 in "Summary" sheet but only change the data source range in sheet "Data". I am using excel 2002 and the folowing code does not work. Sheet("Data").Activate Dim mysource2 as Range With ActiveSheet Set mysource2 = .Range(.Cells(1, 1), .Cells(lrow2, lcol2)) End With Sheets("Summary").Select ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:=mysource2 How do I change a range of an existing Pivot Table without changing anyother settings? Thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
use dynamic data source to make pivot table | Excel Discussion (Misc queries) | |||
Dynamic Pivot Table source | Excel Discussion (Misc queries) | |||
Q: Change pivot table source path? | Excel Discussion (Misc queries) | |||
Change the range of a pivot table data source | Excel Programming | |||
Pivot Table calculated items with dynamic file source | Excel Programming |