How would I ensure a macro runs when excel file is closed?

Question

I have an excel file that loads data into a temporary sheet when the file is opened. The data is then manipulated and stored in another permanent sheet within the file. With both temporary sheet and permanent sheet filled with data, the excel file is becoming huge.
I want to write a macro that will clear contents of the temporary sheet when the file is closed.
Thanks for your help in advance.

Answer 1

You want to put your clean up code in the Workbook_BeforeClose or Worksheet_BeforeClose event code. As the name suggests, Excel will fire the BeforeClose events immediately before the file is closed and will run any VBA code in those subs. If you use the Worksheet version, you would want it in the code sheet for your temporary worksheet. Otherwise, put it in the Workbook code sheet (you will have to specify the name of the temporary worksheet in order to clear it).

Note that you may, or may not, want to set the Application.DisplayAlerts property to false so that you aren't prompted to save the file. If you do want to be prompted, then don't change that to false.

Tim
http://www.tvmcalcs.com/

Answer 2

interior the context of computing and alertness, a malicious program, or purely trojan, is a bite of application which seems to accomplish a undeniable action yet in fact performs yet another collectively with a working laptop or malicious program. opposite to generally happening theory, this action, normally encoded in a hidden payload, would or won't be surely malicious, yet Trojan horses are infamous in the present day for their use interior the setting up of backdoor classes. purely positioned, a malicious program isn't a working laptop or malicious program. in comparison to such malware, it would not propagate through self-replication yet relies upon heavily on the exploitation of an end-consumer (see Social engineering). it fairly is rather a show characteristic that can embody many different varieties of codes. as a result, a working laptop or laptop malicious program or virus would be a malicious program. The term is derived from the classical tale of the malicious program. interior the sphere of laptop shape, 'malicious program' may additionally consult with secure practices loopholes that permit kernel code to get right of entry to something for which it is not authorized.

Answer 3

Hi,
Suppose ur temporary sheet's name is Sheet1. U open VBA window (Alt+F11), open project browser--> within project browser double click "ThisWorkbook" --> then select BeforeClose event of workbook and type the following code:
------------------------------------------------------------------------------
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Sheet1.Columns.Clear 'it will clear everything written in sheet1
ThisWorkbook.Save ' it will save ur workbook and close without asking anything
End Sub
---------------------------------------------------------------------------------