Forum Discussion

Copper Contributor

Dec 07, 2020

Save as name change to cell info

Greetings everyone, First let me start off by thanking everyone in advance. Second I want to apologize. I have no clue what I am doing. I am just a dumb firefighter assigned a task because I was inj...

excel

Macros and VBA

HansVogelaar

MVP

Dec 07, 2020

Jblake

Does this work better?

Sub FileNameAsCellContent()
    Dim FileName As String
    Dim Path As String
    Application.DisplayAlerts = False
    Path = "C:\test\"
    FileName = Range("A6").Value & ".xlsm"
    ActiveWorkbook.SaveAs Path & FileName, xlOpenXMLWorkbookMacroEnabled
    Application.DisplayAlerts = True
    ActiveWorkbook.Close
End Sub

Jblake

Copper Contributor

Dec 11, 2020

HansVogelaar

So unless I am doing something wrong in the macro it is not working. It didnt get any debug issues but when I exit and then I change the name is cell A6 and click save as it doesn't change the name there automatically. I still have to change that name to match what is in cell A6. Not sure!!!!

Thanks

HansVogelaar

MVP

Dec 11, 2020

Jblake

If you want to save the work book automatically when you change the value of A6:

Right-click the sheet tab.

Select 'View Code' from the context menu.

Copy the following code into the worksheet module:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim FileName As String
    Dim Path As String
    If Not Intersect(Range("A6"), Target) Is Nothing Then
        If Range("A6").Value <> "" Then
            Application.DisplayAlerts = False
            Path = "C:\test\"
            FileName = Range("A6").Value & ".xlsm"
            ActiveWorkbook.SaveCopyAs Path & FileName
            Application.DisplayAlerts = True
        End If
    End If
End Sub

This version will leave the name of the active workbook the same, but it should save a copy of the workbook with a name based on A6.