Record/write macros independent of a specific file

Hello I have recorded (i.e. manually as opposed to writing VBA code) a number of macros to perform a routine, however they do not work when I change the filename.

Could someone please advise on how to edit these macros (which reference a specific filename) so that they work when the file name is changed. The macros copy and paste values from different worksheets and then run another set of macros. However all macros and worksheets are located within one excel file


Thank you

Have you tried replacing the hard coded filename with "ThisWorkbook"?

Hope that works for you...

Hello Thanks a lot for your suggestions. Require some clarification

The code (from recording macro) is

Application.Run "Base_Model.xls!Output_calc_"

The issue I have is the macro (Output_Calc) references the specific excel workbook filename (Base_Model.xls)

I would like to alter the code such that if I changed (and saved) the filename of the workbook to say Base_Model_Version_2.xls the macro (Output_Calc) would still run in the saved/renamed file

Need to know which specific part of code I insert ThisWorkbook (or another suggestion) to make it work.

Thanks

Hello I have attached a file which illustrates teh problem. Essentially the second macro (Ctrl + Shift+ A) does not work if I change the file name because the first macro which it activates references the original filename. Any help providing exact instructions to change macro to make this work indepenbent of file name is appreciated. Thank you

forgot to attach file. attached now

Thanks

First Macro (ctrl +A) is a goal seek function: sets s10 = s13 by altering G8

Second macro (Ctrl + shift + A) uses first macro in creating a matrix in sheet A (example attached only creates 4 points in the matrix) by entering adjustment factors in B6 and B7 in sheet B (the adjustemnt factor values are obatined from the matrix row and column headers in sheet A

Adj factors change s10 automatically so macro 2 then runs macro 1 (goal seek) to set s10 back to oiginal target of s13 by altering G8

Example selecting values in cells B1 (-5%)and A2 (05) in Sheet A and pasting them in cells B6 B7 in sheet B will produce a value of 2250. So cell g8 has to be altered (goal seek) until 2250 is reduced to 50 (i.e the target). So new value for G8 is 12.28 (not original value of 20)

Main point however is cannot use Sensitivity Macro (2) because OptCalc macro (1) is filename (Mexa.xls) dependent. This issue is independent of the routine the macro performs. Hope this is clear thanks

I am no expert, I cannot even follow your code completely, but replacing

Please Login or Register  to view this content.

With

Please Login or Register  to view this content.

seems to work just fine. I didn't get any errors, at least.


You can even shorten it to just

Please Login or Register  to view this content.

Thanks a lot. Issue is resolved now

I edited and cleaned up your code a bit. You don't need to select cells to work with them. Also, by specifying sheets, you avoid running your macro from the wrong sheet. If sheet B was active and I ran the code you provided, I would get a much different result than if sheet A was active.

Also, I put both sub-routines into one module.

I am still at a total loss as to what the purpose of your code is, but at least it's a little cleaner.

Please Login or Register  to view this content.