macro to plot from data coming in different columns each time

[roc_on_the_rocks]

Let's say I receive spreadsheets from a Dairy Farm, with over one hundred of cows, but I'm currently tracking milk productions of about six cows (Daisy, Kammi, Lina, Lulu, Madeleine and Shakira). I plot their daily milk production on graphs and do some further data manipulation with them.

I have recorded a macro to automate this tedious and time-consuming task... but the problem is that the cow names - and their data - come in different columns each time I receive a report, requiring me to manually organize the columns in the way they were recorded, which almost defeats the purpose of my macro.

I believe this is a recurring question but I couldn't find anything that I could apply (I did try tweaking few ones, w/o luck). This is Excel 2003.

Could any good soul please shed some light for me? Many thanks in advance.

PS.: Cow names come in different columns in row 31. Daily milk production comes in rows 34 and down, but it varies from report to report, so it would be great if this macro could identify the last row in each report I receive.

Here's what I use today:
Sub Graph_Milk()
'
' Graph_Test Macro
' Macro recorded 3/27/2009 by Ricardo
'
'
Charts.Add
ActiveChart.ChartType = xlXYScatterLinesNoMarkers
ActiveChart.SetSourceData Source:=Sheets("Screen 2_06-09-2009_04-49-59-PM"). _
Range("E4")
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).XValues = _
"='Screen 2_06-09-2009_04-49-59-PM'!R34C1:R2000C1"
ActiveChart.SeriesCollection(1).Values = _
"='Screen 2_06-09-2009_04-49-59-PM'!R34C9:R1695C9"
ActiveChart.SeriesCollection(1).Name = _
"='Screen 2_06-09-2009_04-49-59-PM'!R31C9"
ActiveChart.SeriesCollection(2).XValues = _
"='Screen 2_06-09-2009_04-49-59-PM'!R34C1:R2000C1"
ActiveChart.SeriesCollection(2).Values = _
"='Screen 2_06-09-2009_04-49-59-PM'!R34C14:R1695C14"
ActiveChart.SeriesCollection(2).Name = _
"='Screen 2_06-09-2009_04-49-59-PM'!R31C13"
ActiveChart.SeriesCollection(3).XValues = _
"='Screen 2_06-09-2009_04-49-59-PM'!R34C1:R2000C1"
ActiveChart.SeriesCollection(3).Values = _
"='Screen 2_06-09-2009_04-49-59-PM'!R34C31:R1695C31"
ActiveChart.SeriesCollection(3).Name = _
"='Screen 2_06-09-2009_04-49-59-PM'!R31C31"
ActiveChart.SeriesCollection(4).XValues = _
"='Screen 2_06-09-2009_04-49-59-PM'!R34C1:R2000C1"
ActiveChart.SeriesCollection(4).Values = _
"='Screen 2_06-09-2009_04-49-59-PM'!R34C31:R1695C31"
ActiveChart.SeriesCollection(4).Name = _
"='Screen 2_06-09-2009_04-49-59-PM'!R31C33"
ActiveChart.SeriesCollection(5).XValues = _
"='Screen 2_06-09-2009_04-49-59-PM'!R34C1:R2000C1"
ActiveChart.SeriesCollection(5).Values = _
"='Screen 2_06-09-2009_04-49-59-PM'!R34C36:R1695C36"
ActiveChart.SeriesCollection(5).Name = _
"='Screen 2_06-09-2009_04-49-59-PM'!R31C36"
ActiveChart.SeriesCollection(6).XValues = _
"='Screen 2_06-09-2009_04-49-59-PM'!R34C1:R2000C1"
ActiveChart.SeriesCollection(6).Values = _
"='Screen 2_06-09-2009_04-49-59-PM'!R34C39:R1695C39"
ActiveChart.SeriesCollection(6).Name = _
"='Screen 2_06-09-2009_04-49-59-PM'!R31C39"
ActiveChart.Location Where:=xlLocationAsNewSheet
With ActiveChart
.HasTitle = False
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
End With
End Sub

 

[roc_on_the_rocks]

* bump *

Please help...

 

[roc_on_the_rocks]

Is this request unclear? I'll be glad to respond to any questions or to exemplify it better.

Please help...

 

[roc_on_the_rocks]

I simplified my question. I hope it helps in obtaining an answer(s) . I greatly appreciate any help.

Let's say I analyze atmospheric conditions data by the hour. The data is collected by several sources, meaning that the variety of data (i.e., columns) changes by the data provider. However, data will always have a column containing 'Ambient_Temperature' data, but I cannot predict to which column it will be in.

Can someone please help me to create a VBA macro to plot XY Scater chart, where X is the 'time_stamp' in column B and Y is 'Ambient_Temperature', in an unpredictable column. The data names ( 'time_stamp' and 'Ambient_Temperature') are located in row 8 and actual data in rows 11 and below. The macro I created is only capable to read 'Ambient_Temperature' data if it comes from the same column all the time, which is not the case.

Thank a lot!

 

[roc_on_the_rocks]

** bump **

 

[roc_on_the_rocks]

I really hoped someone could give at least give me an initial push here. Absolutely zero answer is disappointing. Is this too trivial? Too difficult?

Thanks again,