VBA : Me.Name function

user_14002405 0
Viewed 195

I have a spreadsheet xlsm "My_workfile1", I would like to Save As this file like "My_final_workfile_1.xlsm".
I would like to hold all data in "My_final_workfile_1" and have the cells C4,C6,C7,C11,C12 in initial file "My_workfile1" empty after closing.

My code is:

Sub logFormState()
    Sheets("1 - Feuille de Suivi ").Range("C4") = ""
    Sheets("1 - Feuille de Suivi ").Range("C6") = ""
    Sheets("1 - Feuille de Suivi ").Range("C7") = ""
    Sheets("1 - Feuille de Suivi ").Range("C11") = ""
    Sheets("1 - Feuille de Suivi ").Range("C12") = ""
End Sub

Public Sub Workbook_BeforeClose(Cancel As Boolean)
    If Me.Name = "My_workfile1" Then
        Call logFormState
    End If
End Sub

But Me.Name function doesn't work. I'm wondering if my code is correct.

Thank you for your help!

vba excel
1 Answers

Workbook BeforeClose

The Code

Option Explicit

Sub DebugPrint()
    With ThisWorkbook
        Debug.Print "Workbook:"
        Debug.Print """" & .FullName & """"
        Debug.Print """" & .Path & """"
        Debug.Print """" & .Name & """"
        Debug.Print "Worksheet Names:"
        Dim ws As Worksheet
        For Each ws In ThisWorkbook.Worksheets
            Debug.Print """" & ws.Name & """"
        Next ws
    End With
End Sub

Sub logFormState()
    ' Note that this worksheet name ends with a space!
    With ThisWorkbook.Worksheets("1 - Feuille de Suivi ")
        .Range("C4,C6,C7,C11,C12") = ""
        ' Either save here...
        .Parent.Save
    End With
End Sub

Public Sub Workbook_BeforeClose(Cancel As Boolean)
    If Me.Name = "My_workfile_1.xlsm" Then
        ' Change the path if necessary.
        Me.SaveCopyAs Me.Path & "\" & "My_final_workfile_1.xlsm"
        Call logFormState
        '... or save here:
        '.Save
    End If
End Sub
user_98140690
Related