MS Excel Macro: Dynamic Graphing using Named ranges


New Member
Mar 18, 2009
Hello everyone,

I am trying to create a process which will take my data and convert it into a graph. I plan to use macro so that I can convert my data into graph everyday, instantly, with out having to go through the whole process like I do right now.

So I was able to create a macro which converts data into graph. However, this macro only works for file 'Book1'. I think I know what the problem is.
To graph, I am using named ranges to create dynamic charts. ( Please see ). Now in my source data of my graph, the value formula "Sheet1!time" changes to "Book1!time". Now the problem is that if I run this macro in a new file it doesn't work.

The following line gives me an error. As you can see, "Book1" is the problem. Is there a way I could change "Book1" to "Sheet1" ?

Windows("Book1").SmallScroll Down:=18 'error on this line
ActiveSheet.Shapes("Chart 1").IncrementLeft -32.25
ActiveSheet.Shapes("Chart 1").IncrementTop 331.5
Windows("Sheet1").SmallScroll Down:=42
ActiveSheet.Shapes("Chart 1").IncrementLeft -60.75
ActiveSheet.Shapes("Chart 1").IncrementTop 544.5
Windows("Sheet1").SmallScroll Down:=27
ActiveSheet.Shapes("Chart 1").IncrementLeft -45.75
ActiveSheet.Shapes("Chart 1").IncrementTop 375.75
ActiveSheet.Shapes("Chart 1").ScaleWidth 1.44, msoFalse,


Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
'Scroll' events will just visually move the scroll bar. You don't need them to create graphs. Try commenting them all out by adding ' in front of those lines and see if it works.
Upvote 0
thanks alot Sal. The macro seems to be working. But now, I need a code which will move my chart to the bottom of the page, possibly two cells below to the the last data entered. Also then I want to be able to print just the chart and the two sells below the chart. Basically, I want a Print Area setup. I can do it manually but I don't know how to do it with code.

Upvote 0
Ok guys, I am not going to worry about the print area problem for now. So going back to my first post, I mentioned that I want to change 'Book1' to 'Sheet1' in my code. In the code below (its just an extension of code that I pasted in my first post above), any line that has 'BOOK3' in it is giving me an error. I need to remove 'Book3' from the code and write 'Sheet1'. Lines third last and fourth last are important and I can not put " ' " infront of them.


ActiveSheet.ChartObjects("Chart 1").Activate
' ActiveSheet.SmallScroll Down:=87
ActiveWindow.Visible = False
ActiveWorkbook.Names.Add Name:="time", RefersToR1C1:= _
ActiveWorkbook.Names.Add Name:="rate", RefersToR1C1:= _
ActiveWorkbook.Names.Add Name:="rate", RefersToR1C1:= _
ActiveWindow.SmallScroll Down:=-42
ActiveWindow.SmallScroll Down:=99
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.SeriesCollection(1).XValues = "=Book3!time"
ActiveChart.SeriesCollection(1).Values = "=Book3!rate"
' Windows("Book3").SmallScroll Down:=93
End Sub
Upvote 0

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back