New macros in excel 2007 vs 2003

March 17, 2009 at 13:02:01
Specs: Windows XP
Please forgive me if this has already been answered, but I didn't find what I'm looking for.

I'm trying to figure out why in excel 2007 when I record a macro that does simple things such as delete rows, columns, and sorts, that macro will tag itself to a particular worksheet / file and not stay generic.

By that I don't mean that I am saving the macro so that it only works for that one file, but that even though the macro is accessible in any excel document I open, it will not work because the select range references the file / workbsheet that the macro was created in.

When I used to create macros in excel 2003, it would select a range and that range would work in any excel document I opened.

I hope this makes sense, and that someone can help me with this.

Thank you very much.

See More: New macros in excel 2007 vs 2003

Report •

March 17, 2009 at 13:39:24
I don't have 2007, so I can't duplicate your problem.

The little I do know about Macros in 2007 is that by default they are saved with Absolute References. You have to click the Use Relative References button in the Developers toolbar to be able to run them based on whereever the cursor is.

Don't know if that's your problem or not...

Report •

March 18, 2009 at 08:14:06
Thanks for the reply, however that didn't do it. I've copied a line from the macro that will hopefully better illustrate the problem.

In 2007 it comes up like:

ActiveWorkbook.Worksheets("Detail Report Mar 12 X+4").Sort.SortFields.Clear

How do I remove the reference to a particular worksheet, and keep it generic here so that when I run it in another excel file it won't be looking for "Detail Report Mar 12 X+4"?

I do however still want to maintain absolute references and not relative references.


Report •

March 18, 2009 at 09:02:57
re: however that didn't do it.

Just for clarification, didn't do what?

Clicking the button after recording the macro won't help. You'd have to click it before recording so it doesn't write the worksheet name into the code - if that is even the problem. Like I said, I don't have 2007 so I've never seen the code created by the recorder, other than in some tutorial videos.

Anyway, this seems pretty generic to me:

Don't know...give it a try.

Report •

Related Solutions

March 19, 2009 at 06:31:40
Replacing the reference with ActiveSheet seems to fix my problem.

Thanks :)

Report •

Ask Question