running a macro from a different workbook

[chcreswell]

I am downloading excel spreadsheets from the web that each have the exact same format, except for different states. I want to extract this information to a sheet on my network with different formatting. I have written a macro that performs this task fine, but the macro was written in the workbook that I downloaded. I want to have the macro on a local spreadsheet and run it from the sheet that I download. I thought that because the sheet I downloaded was the active sheet, all of the commands would work, but the first time that I try to access a worksheet on the downloaded workbook when running the macro form a local spreadsheet I get an Object Required Error.
Does anyone know a good way around this problem.

Thank you

[Juan Pablo González]

Well, without the specific problem it's hard to say, but if you're using ActiveSheet, ActiveWorkbook and such, the code should work.

Now, if you want to run code from another workbook (Your subject) is something like

Application.Run "Book1.xls!Test"

[chcreswell]

calling the macro like you said would work fine, however I can't specify the workbook name because each time I download it, the name is slightly differnt. Somehow I need to tell it to run the macro off of the current workbook

I tried
Application.Run ActiveWorkbook!ViewComm1

where ViewComm1 is the name of the Macro that I want to run, but it didn't take.
Any suggestions

[NateO]

Try:

Application.Run ActiveWorkbook.Name & "!ViewComm1"

[chcreswell]

Perfect, worked like a charm

You guys rock!