Okay, here is the code that should work. In this sample code, the source workbook has been named "Trial1.xlsm" and the source worksheet as "Sheet1"; the destination workbook as "Trial2.xlsx" and destination worksheet as "Sheet1". The file path to the destination folder has been given as"C:\Trial2.xlsx". You have to change these names and file path according to what you have on your system.
Enter this macro in "This Workbook" code window of the Project Explorer. To do this, open your source workbook, and press Alt+F11. It will open the Macro code window. On the left hand side is the Project Explorer window. Double Click on "This Workbook" and enter the attached code in the blank window that shows up on the right after double clicking.
It will be difficult to explain how to link this to the Application.BeforeClose event of Excel, which is what you actually wanted. I suggest that you assign the code I am giving to a button on your Quick Access tool bar (QAT). You can search on the net to find out how to assign a macro to a button on the QAT. You can export the data in the two columns by clicking on the button.
|