613Views6Replies

Author Options:

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


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.

6 Replies

user
lemonie (author)2011-07-19


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

Select as Best AnswerUndo Best Answer

user
LucasOchoa (author)lemonie2011-07-19

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.

Select as Best AnswerUndo Best Answer

user
lemonie (author)LucasOchoa2011-07-19


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

Select as Best AnswerUndo Best Answer

user
LucasOchoa (author)lemonie2011-07-19

But yes as a variable to clerify

Select as Best AnswerUndo Best Answer

user
steveastrouk (author)2011-07-19

WHY do people insist on Excel for data reduction when there are vastly better packages for handling science data ? MathCad, Octave, TeeChart......

Select as Best AnswerUndo Best Answer

user
wearetheworld (author)2011-07-19

You may need to specify at least a partial path (eg, \mydatafiles\sheetname). Or a full path (eg, c:\excelsheets\sheetname. Just a guess.

Select as Best AnswerUndo Best Answer