trending data from a csv file with one macro with changing worksheet name.

[Ray Morton]

I currently have multiple csv files from a SCADA computer system that I am converting to excel spreadsheets. All of these files have a designated name like "TANKS000.csv". If the files are not converted within a 24 period then The SCADA Program will create another CSV file named "TANKS001.csv". Here is my problem, how can I write the macro to trend my data to a chart no matter what the worksheet is named? Here is a sample of the macro where I am getting hung up.

ActiveChart.SetSourceData Source:=Sheet("TANKS000").Range("B8:M103"), _
PlotBy:=xlColumns
I need help!!!!

 

[Tom Ogilvy]

If you only have one sheet that contains the name TANKS

for each sh in thisworkbook.Worksheets
if instr(1,sh.name,"tanks",vbTextCompare) then
set sh1 = sh
exit for
end if
Next
ActiveChart.SetSourceData Source:=sh1.Range("B8:M103"), _
PlotBy:=xlColumns

or based on your later post

for each sh in thisworkbook.Worksheets
if instr(1,sh.name,"tanks",vbTextCompare) then
sh.name = "Tanks"
exit for
end if
Next
ActiveChart.SetSourceData Source:=Sheet("TANKS").Range("B8:M103"), _
PlotBy:=xlColumns

--
Regards,
Tom Ogilvy

I currently have multiple csv files from a SCADA computer system that I

am converting to excel spreadsheets. All of these files have a designated
name like "TANKS000.csv". If the files are not converted within a 24 period
then The SCADA Program will create another CSV file named "TANKS001.csv".
Here is my problem, how can I write the macro to trend my data to a chart no
matter what the worksheet is named? Here is a sample of the macro where I am
getting hung up.