MS Excel Macro: Dynamic Graphing using Named ranges

[hero85]

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 http://office.microsoft.com/en-us/excel/HA011098011033.aspx ). 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,

Thanks,

 

[Sal Paradise]

'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.

 

[hero85]

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.

thanks.

 

[DRANDON]

Use the Macro- Record as often as you need..

 

[hero85]

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.

Thanks.



'
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.PlotArea.Select
' ActiveSheet.SmallScroll Down:=87
ActiveWindow.Visible = False
'Windows("Book3").Activate
Range("H113").Select
ActiveWorkbook.Names.Add Name:="time", RefersToR1C1:= _
"=OFFSET(Sheet1!R1C3,1,0,COUNTA(Sheet1!C3)-1)"
ActiveWorkbook.Names.Add Name:="rate", RefersToR1C1:= _
"=OFFSET(Sheet1!R1C8,1,0,COUNTA(Sheet1!C8)-1)"
ActiveWorkbook.Names.Add Name:="rate", RefersToR1C1:= _
"=OFFSET(Sheet1!R1C8,1,0,COUNTA(Sheet1!C8)-1)"
ActiveWindow.SmallScroll Down:=-42
Range("J8").Select
ActiveWindow.SmallScroll Down:=99
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.Axes(xlValue).MajorGridlines.Select
ActiveChart.ChartArea.Select
ActiveChart.SeriesCollection(1).XValues = "=Book3!time"
ActiveChart.SeriesCollection(1).Values = "=Book3!rate"
' Windows("Book3").SmallScroll Down:=93
End Sub