Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data ranges for Graphs & pivot tables in macros
I am having trouble generating graphs and pivot tables
with a macro from the correct data. I used the relative reference feature when recording the macro, but the visual basic code is always relying on the specific range I used when it actually creates the new item. Example (Graph then pivot table): Range("A2:C2").Select Range(Selection, Selection.End(xlDown)).Select ActiveCell.Range("A1:C19").Select Charts.Add *** Range(Selection, Selection.End(xlToRight)).Select Range(Selection, Selection.End(xlDown)).Select ActiveWorkbook.PivotCaches.Add (SourceType:=xlDatabase, SourceData:= _ "Sheet1!R4C1:R227C42").CreatePivotTable TableDestination:="", TableName:= _ "PivotTemp_1" This works fine this time, but the next time I run the macro I'll have more data and this won't recognize anything below the original data block boundaries. Also, if anybody's looking for extra credit, I'd can't figue out how to select a range that doesn't include a summary row at the bottom. In this case, say that A1 contains a Header, A2:C2 contain column labels, A3:C19 contain data (which will expand several rows before the next time I run the macro), and A20:C20 is a summary row. I need to find a way to start at A2 and select A2:C19 using relative references. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dynamic data ranges in pivot tables | Excel Discussion (Misc queries) | |||
Pivot Tables and Graphs (charts) | Charts and Charting in Excel | |||
Pivot Tables with Multiple ranges | Excel Discussion (Misc queries) | |||
excel graphs data labels in Pivot tables | Charts and Charting in Excel | |||
Problem with times in pivot tables and graphs | Excel Discussion (Misc queries) |