Forum Discussion
new VBA code release and update of several WB with the same VBA code
I need to update VBA code in several WB's in different folders.
I do not want to open those WB's to change the code. Every WB has the same code. Can you suggest me how to do this only once? Or can I contruct a WB 'clone' containing the code in such a way that every WB can use the updated code?
3 Replies
Hi,
You need to update the workbooks, one by one!
To my knowledge, this is no way to do it at once!
But think about another solution to make the code only in one place and each workbook has an access to this place so that if you want to update the code in the future, you just update it one time, in one place.
By the way, this is possible in Excel!
You can save the macro in the Personal Macro Workbook, in order to make it available to all open workbooks.
For more information, please check out this https://trumpexcel.com/personal-macro-workbook/.
Hope that helps
Yes, I used the tool you refer but I have a problem. In every workbook say A.xlsm I have two VBA Project the first is the VBA project of the workbook A itself and the other is the VBA Project Personal.xslb which contains the code to be executed by every workbook. When this code refers to sheets and cells of A I have a run time error probably because code is working in the workbook Personal.xslb. Is there any way to refer to sheets and cells of the workbook A?
Hi,
From the beginning, I supposed that the code stored in every workbook is exactly the same!
Which means that the code hasn't a specific sheets names or range names associated with one workbook without the other, and the code is applied to the same cell ranges.
But you can store the code in the Personal Macro Workbook and make it generic by using some tricks.
For example, you could use the below code to apply the code to the active sheet of the active workbook instead of a specific sheet name in a specific workbook:
ActiveWorkbook.ActiveSheet.Range("A1").SelectYou could also use IF statement to check if a specific workbook is active and apply a specific code to it.
Regards
