Macro will not work in NEW workbook

[Guest]

I created a macro in workbook #1 and am having it create a new workbook with macro code in it. However the macro says it can't find the file. I think it is because it is looking for it in the old workbook. Can't it be done so that the code with look for it in the current workbook and not the old one?

 

[Steve Garman]

I'm having trouble following your description of the problem.

Perhas you could show us some code that doesn't work and tell us what
error message you are getting.

 

[Guest]

This code is in the worksheet (I tried it in the workbook also) When I do a save as and create another workbook with a different name and try to run this macro with the button, it says 'name of file.xls' could not be found. Check the spelling of the file name and verify that the file location is correct. Thanks for your help.

Sub RemoveCalcs10(
If MsgBox("Are you sure? This will remove calcs from Reading Differences!", vbYesNo + vbQuestion) = vbNo
Then Exit Su

MsgBox ("You can now manually put in your clearances "
Application.ScreenUpdating = Fals
Password = "MECH
ActiveSheet.Unprotect Passwor
Range("C39:L44").Selec
Selection.Locked = Fals
Selection.FormulaHidden = Fals
Range("C47:L52").Selec
Selection.Locked = Fals
Selection.FormulaHidden = Fals
Range("C55:L60").Selec
Selection.Locked = Fals
Selection.FormulaHidden = Fals
Selection.ClearContent
Range("C47:L52").Selec
Selection.ClearContent
Range("C39:L44").Selec
Selection.ClearContent
Range("C39").Selec
Password = "MECH
ActiveSheet.Protect Password, True, True, Tru
Application.ScreenUpdating = Tru
End Su

----- Steve Garman wrote: ----

I'm having trouble following your description of the problem

Perhas you could show us some code that doesn't work and tell us what
error message you are getting

Cecil wrote

 

[Steve Garman]

Are you sure the button is running the right macro?

Try pressing the "Debug" button when you get the errormessage and let us
know which line is causing the error.

 

[Steve Garman]

Sorry, I'm being really dim,aren't I?

Having re-read the thread from the beginning, I think I understand your
problem.

I recommend that you take the code and put it into a "personal.xls"
workbook.
You can create this using the macro recorder and recoding any simple
action. Set "Store macro in" to "Personal Macro Workbook"

Paste your macro into personal.xls

Change your button to point at the new version. It will then always be
available at startup.

Are you sure the button is running the right macro?

Try pressing the "Debug" button when you get the errormessage and let us
know which line is causing the error.

<snip>

 

[Guest]

There is no debug, it comes up with Microsoft Excel warning. It says the macro can not be found. When I open the button to see what macro it is attached to, it shows it being tied to the old workbook('filename.xls'!Sheet1.RemoveCalcs10) When I close the old workbook, then the "Assign Macro" points to 'C:\Data\filename.xls'!Sheet1.RemoveCalcs1

I can put the cursor in the code and then run it, then I can run the debug, it stops on the row under the first ActiveSheet.Unprotect Password Which is Range("C39:L44").Selec

(The old workbook works fine) Also as long as the old file is on the same computer it will open the file when I click on the button in the new workbook.

Will the macro automatically follow the new workbook since it has a different file name?
----- Steve Garman wrote: ----

Are you sure the button is running the right macro

Try pressing the "Debug" button when you get the errormessage and let us
know which line is causing the error

Cecil wrote

 

[Guest]

Sorry, forgot to add the erro

the Run-time erro '1004
Select method of Range class faile

 

[Guest]

I did that also and the new workbook that is on a different computer just says, 'Personal.XLS' could not be found....

----- Steve Garman wrote: ----

Sorry, I'm being really dim,aren't I

Having re-read the thread from the beginning, I think I understand your
problem

I recommend that you take the code and put it into a "personal.xls"
workbook
You can create this using the macro recorder and recoding any simple
action. Set "Store macro in" to "Personal Macro Workbook

Paste your macro into personal.xl

Change your button to point at the new version. It will then always be
available at startup

Steve Garman wrote

Are you sure the button is running the right macro
know which line is causing the error

<snip>

 

[Steve Garman]

I hadn't realised we were talking about different computers.

Looks like it's time to build an addin.

 

[Guest]

I guess I am bewillered that a code can just say, ActiveWorkbook and it knows that no matter what the name of it is, it will work. But if it is on a different computer, it doesn't know to use the active workbook? Am bout ready to give up.

----- Steve Garman wrote: ----

I hadn't realised we were talking about different computers

Looks like it's time to build an addin

Cecil wrote