Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
Same cell range on different sheet
I have a workbook with many sheets containing the same type of data.
The user has made charts on one sheet and would like to copy them to all of the other sheets, and have each chart reference the same area of data on the new sheet, i.e. it should reference, say, A1:B20 on whatever sheet it's on. Is there a way to access individual parts of the series source in VBA? I can write a procedure to retrieve the xvalues and values for the series, but I don't know how to separate out the sheet name from the range/cells reference. If this can be done by hand, he'll be just as happy to copy and paste the set of charts to each worksheet & have it re-link to the same area of the new sheet. Ideally there will be a final product where he can tinker with charts on one page and spend less than 5 minutes either clicking an "update all sheets" button or copying the entire set of charts to every page. I would go for a macro, but it must be something he doesn't need to worry about, otherwise he'd rather find the way to copy & paste. Thanks for any insight. |
#3
Posted to microsoft.public.excel.charting
|
|||
|
|||
Same cell range on different sheet
I wrote a utility to handle this:
http://peltiertech.com/Excel/Charts/ChgSrsFmla.html - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ wrote in message oups.com... I found the function I needed on another site, thanks to Andy for his posts there. The solution is Replace which searches a string for text and replaces it with new text. I copied and pasted the charts then searched the new chart formulas for the old sheet name & replaced it with the new name. Here's the line I needed: With Worksheets(sh).ChartObjects(i).Chart.SeriesCollect ion(1) .Formula = Replace(.Formula, modelsheet, current) End With wrote: I have a workbook with many sheets containing the same type of data. The user has made charts on one sheet and would like to copy them to all of the other sheets, and have each chart reference the same area of data on the new sheet, i.e. it should reference, say, A1:B20 on whatever sheet it's on. Is there a way to access individual parts of the series source in VBA? I can write a procedure to retrieve the xvalues and values for the series, but I don't know how to separate out the sheet name from the range/cells reference. If this can be done by hand, he'll be just as happy to copy and paste the set of charts to each worksheet & have it re-link to the same area of the new sheet. Ideally there will be a final product where he can tinker with charts on one page and spend less than 5 minutes either clicking an "update all sheets" button or copying the entire set of charts to every page. I would go for a macro, but it must be something he doesn't need to worry about, otherwise he'd rather find the way to copy & paste. Thanks for any insight. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
UDF is updateing cells on another sheet with count from current sheet. | Excel Discussion (Misc queries) | |||
Urgent date/scheduling calc needed | Excel Worksheet Functions | |||
Possible Lookup Table | Excel Worksheet Functions | |||
Copy cell format to cell on another worksht and update automatical | Excel Worksheet Functions | |||
Copy text from same cell on every sheet to title sheet? | Excel Discussion (Misc queries) |