How can i make the code for an excel macro be universal and not only work for one file?


So i am writing a macro to graph a large set of data. After finishing writing the macro it worked fine on its original fie in this case 234
here is one of the original sets of key data.

ActiveChart.SeriesCollection(1).XValues = "='234'!$A$27:$A$1490"

It specifies the 234 as the file to retrieve the data.

I assumed that after replacing the 234 in the line of code above with a command like sheetname. like so below.

ActiveChart.SeriesCollection(1).XValues = "='sheetname'!$A$27:$A$1490"

Who ever can successfully answer will receive a patch. Feel free to ask questions because what i wrote above is a slight bit vauge.

sort by: active | newest | oldest
lemonie6 years ago

Don't start paragraphs with "So", it makes no sense.

Do you want to use "sheetname" as a variable, or to reference a sheet within a specific workbook?

L
LucasOchoa (author)  lemonie6 years ago
Thats exactly what i have written in the code above, thanks for the writing advice, And putting parenthesis around the command sheet name would literally make the macro call up the word sheetname.

For use as a variable, you would have the macro look it up from a specific cell, into which you put the variable I'd guess. - is that the sort of thing you want?
I've not fiddled with macros much...

L
LucasOchoa (author)  lemonie6 years ago
But yes as a variable to clerify
WHY do people insist on Excel for data reduction when there are vastly better packages for handling science data ? MathCad, Octave, TeeChart......
You may need to specify at least a partial path (eg, \mydatafiles\sheetname). Or a full path (eg, c:\excelsheets\sheetname. Just a guess.