How do I paste a source data reference to a chart using a macro?
I need to be able to draw a new graph as I change the parameters, but that
means that I would have to re-enter the source reference for the X and Y values. In order to do that I concatenated a string that builds the address, then I copy it and paste it to the chart's source references. I need to do that with a macro. When I run the Excel macro utility to recreate all my steps it inserts a reference that remains permanent. When I run the macro again it does not paste the new reference but remains with the old one I inserted previously. |
How do I paste a source data reference to a chart using a macro?
I realized that I need to explain myself better. I first create a string
with the address as follows: =CONCATENATE("=Data!A$4:A$",TEXT(4+H3,"#")), the I copy and paste special the value to another address. Below is the macro subroutine to copy the resulting string: Keyboard Shortcut: Ctrl+w ' ActiveSheet.ChartObjects("Chart 1").Activate ActiveChart.ChartArea.Select ActiveWindow.Visible = False Sheets("S-Curve").Select ActiveSheet.ChartObjects("Chart 1").Activate ActiveChart.ChartArea.Select ActiveChart.SeriesCollection(1).Values = "=Data!R4C1:R16C1" ActiveWindow.Visible = False Sheets("Data").Select Range("K8").Select Sheets("S-Curve").Select ActiveSheet.ChartObjects("Chart 1").Activate ActiveChart.ChartArea.Select ActiveChart.SeriesCollection(1).XValues = "=Data!R4C3:R16C3" ActiveWindow.Visible = False Range("C2").Select End Sub "Pedro J. Davila" wrote: I need to be able to draw a new graph as I change the parameters, but that means that I would have to re-enter the source reference for the X and Y values. In order to do that I concatenated a string that builds the address, then I copy it and paste it to the chart's source references. I need to do that with a macro. When I run the Excel macro utility to recreate all my steps it inserts a reference that remains permanent. When I run the macro again it does not paste the new reference but remains with the old one I inserted previously. |
All times are GMT +1. The time now is 08:51 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com