Forum Discussion

Jblake's avatar
Jblake
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
Jblake's avatar
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's avatar
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.

Resources