VBA code deleted in macro enabled workbook when i overwrite workbook with save as

[9davros8]

Hi guys


I have not used VBA for a long time so bear with me.

I have a VBA code that works in a macro enabled workbook – so the VBA code is specific to that book. However, every 2 months I need to overwrite this specific workbook and in the process the VBA code is deleted. I found out through trial and error that the code needs to be in this macro enabled workbook for it to work because it is triggered by a double click event. The workbook is the always the same name.


So, the question is how do I stop the code being deleted when I overwrite the specific workbook it is associated with?

And help appreciated

Thanks

 

[RoryA]

What are you overwriting it with? If it's a different workbook that doesn't have the code in it, that is your problem.

 

[9davros8]

hi

basically i am creating a workbook called A.xlsx and on this workbook i run a macro the end result is that it saves this workbook as A.xlsm. However A.xlsm already exists so i have to overwrite it. the VBA code in this sheet is then deleted. From reading online the VBA code (it's a double click event) must be 'stored' in this workbook. I've tried storing the code in my personal workbook but am unable to 'call' it from there. any suggestions would be appreciated as the code does work.

thanks

 

[RoryA]

Why do you have to create a new workbook rather than just amending the existing one?