Saving as stops macro working

hi

Im wondering if there is an easy way around a problem i have with my worksheet. i have a worksheet named for example 'allocation 1' this is a master document and is opened and modified and 'saved as' under a customer name. This then stops a few important macros working properly because they refer to the original title and not the new saved title. Is there any code that will let the macro recognise any new title it is saved under?

Many Thanks

Denzil

Hi Denzil

Where necessary any references to the workbook name should be replaced with "Activeworkbook" then all macros should function normally.

Regards

Jeff

Hi Jeff

Im not sure whats goin wrong but if I add "activeworkbook" in the macros where the original name was and resave, the macro still doesnt work on debug the code stops at that same line where i put your text in, Is there something i am doing wrong or could the problem be elsewhere?

Many thanks for your help

Denzil

-----bump-----

.............bump..........

You have included a workbook with 14 modules in it. It helps you have given us a workbook, but it would be more helpful to describe what exactly does not work. Which macro it involves, rather than expecting us to try everything until something does not work.

If you are a little more descriptive, with the error or problem I am sure you will get an answer

Regards

Dav

Hi Dav

Thanks for your reply

I see what your saying, I thought Id mentioned the button which uses the macro that does not work. Its the "Finished Spec Button" and the code is:

PHP Code: 

Sub Macro2()
'
' Macro2 Macro
' Macro recorded 09/01/2008 by
'

'
    Sheets("Allocation").Select
    Application.Run "'Par 2 Auto Spec & Allocation 04.06.07side.xls'!test"
End Sub 


I Know its the "Par 2 Auto Spec & Allocation 04.06.07side.xls" Line but Im not sure what text to put in its place that could mean something more universal so it does not need that as the saved title for the macro to work.

Any Ideas?

Many Thanks

Denzil

Well I would change the test macro to make it select the sheet allocation. If you are allready on this sheet, it just reselects the current sheet, so the code will still run fine.

Please Login or Register  to view this content.

then I would assign the test macro to the button instead of macro2. Macro2 can even be deleted, as all it is doing is duplicating code

Does that help?

Regards

Dav

Hi Dav

Very Simple And very effective!!

Works exactly how I want it to

Thanks for your help, much appreciated!!