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 injured. I have never used Excel for more then less then basic things. 

I have a file that i was told to work on that is for many different address in the town I work for. I need to change some data in each and save the fie as that address. The cell for that address is A6 on that work sheet. I would like to be able to change the address in that cell and hit the save button and it change the name and save that file to a designated spot on my computer. This way I can change the info and save as each time with out having to retype the new address again in the save as section. I will be doing this for almost 5000 addresses so any way possible to stream line this would be awesome. Is this possible. I know it is done in the Macro section and I though that I had the right info entered but when I check to debug it I always get an error.

Here is the info I found and copy and pasted into the Macro

 

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

 

The error I keep getting is 

Run-time Error 1004

Method 'SaveAS' of object ' _workbook'Failed

 

Please help 

excel
Macros and VBA

3 Replies

  • HansVogelaar's avatar
    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'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