Hello everyone,

We are studying meteorological engineering in istanbul and kind of nerd of excel and programming. We need help with making graph for lots of files almost 1000 files. It is ozone layer for every day for 10 years. We have to make grapp of it for our graduation project. Source data ( i mean the location of the data) in every excel file is the same, we are making auto macro in excel but it doesn't work because it defines the source data with the sheet name. Every file has its own sheet name depending of the it date. We will be appreciated if you help us. I'm posting the code that we made and glad to hear for any correction. thank you all.



Sub bitirme()
'
' bitirme Makro
'

'
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlXYScatter
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).XValues = _
"='20080116.ECC.Z.z11791.RMIB'!$B$489:$B$936"
ActiveChart.SeriesCollection(1).Values = _
"='20080116.ECC.Z.z11791.RMIB'!$H$489:$H$936"
ActiveChart.DisplayBlanksAs = xlZero

End Sub



The only thing that we need is to change the sheet name ('20080116.ECC.Z.z11791.RMIB'!) to active sheet but it doesnt work if you do it like that.

ActiveChart.SeriesCollection(1).XValues = _
"='20080116.ECC.Z.z11791.RMIB'!$B$489:$B$936"
ActiveChart.SeriesCollection(1).Values = _
"='20080116.ECC.Z.z11791.RMIB'!$H$489:$H$936"