Solved: Run-time error 1004 (macro export/import?)

[Lester]

Hello again,
Thanks for the help thus far...I have another query.
I recorded the following macro, then exported it for use with other excel files:
[vba]Sub CalculateCostandSales()
'
' CalculateCostandSales Macro
' Macro recorded 01/11/2007 by Les
'
' Keyboard Shortcut: Ctrl+g
'
Application.Run "'Temp Company Data.xls'!ImportLookUp"
Sheets("Sheet6").Select
Application.Run "'Temp Company Data.xls'!ProcessSalesAndCosts"
End Sub[/vba]
I can import it into the new excel file ok, but the problem is that it still contains references to the original excel file 'Temp Company Data.xls' where the macro was recorded.
Hence, I get the following '1004' run-time error.
'Temp Company Data.xls' could not be found. Check the spelling of the file name, and verify that the file location is correct.

The debugger then highlights the line [vba]Application.Run "'Temp Company Data.xls'!ImportLookUp[/vba]

Does anyone know how to make the macro 'generic' so it can work in any excel file that imports it?

Many thanks for your help.
Lester.

[Bob Phillips]

Surely it depends upon which macro you want to run, and where it is? You have to tell the code that somehow. If it is the same workbook as that code, you don't need Application.Run.

[Lester]

Surely it depends upon which macro you want to run, and where it is? You have to tell the code that somehow. If it is the same workbook as that code, you don't need Application.Run.

Hi - each of the macros 'ImportLookUp' and 'ProcessSalesAndCosts' have also been imported into the new excel file...'ImportLookup' works as expected when run on its own.

Is it possible to simplify the troublesome line, thus?:
[vba]Application.Run "ImportLookUp"[/vba]
or maybe just
[vba]ImportLookUp[/vba] ?

[Bob Phillips]

yes, but as I said, if it is the same workbook, you don't need Application.Run

[Lester]

yes, but as I said, if it is the same workbook, you don't need Application.Run

Excellent! It works! Job's a good 'un!
Cheers