Creating a Document in same folder when another document is opened

[tonywatsonhelp]

Hi Everyone,
OK So heres what I need,
I need two macros,
1 when the document is opened it creates a new document in the same folder called "Used"
When the document is closed it deletes the docment called "Used"

how can I do this.

Thanks

Tony

 

[Yongle]

place code in ThisWorkbook module

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    DeleteUsed
End Sub

Private Sub Workbook_Open()
    DeleteUsed
    CreateUsed
End Sub

Private Sub DeleteUsed()
    On Error Resume Next
    Kill ThisWorkbook.Path & "\Used.xlsx"
End Sub

Private Sub CreateUsed()
    Application.ScreenUpdating = False
    Dim wb As Workbook
    Set wb = Workbooks.Add
    Application.DisplayAlerts = False
    wb.SaveAs Filename:=ThisWorkbook.Path & "\Used.xlsx"
    Application.DisplayAlerts = True
    wb.Close False
End Sub

VBA will not do what you require if placed in incorrect module

 

[Macropod]

1 when the document is opened it creates a new document in the same folder called "Used"
When the document is closed it deletes the docment called "Used"

Word uses documents, Excel uses workbooks. To avoid confusion, please use the standard MS Office terminology.

 

[tonywatsonhelp]

Thank you Yongle, this is perfect.
Thanks for your help.
Tony

 

[Yongle]

thanks for your feedback