Save as name change to cell info

%3CLINGO-SUB%20id%3D%22lingo-sub-1963137%22%20slang%3D%22en-US%22%3ESave%20as%20name%20change%20to%20cell%20info%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1963137%22%20slang%3D%22en-US%22%3E%3CP%3EGreetings%20everyone%2C%3C%2FP%3E%3CP%3EFirst%20let%20me%20start%20off%20by%20thanking%20everyone%20in%20advance.%20Second%20I%20want%20to%20apologize.%20I%20have%20no%20clue%20what%20I%20am%20doing.%20I%20am%20just%20a%20dumb%20firefighter%20assigned%20a%20task%20because%20I%20was%20injured.%20I%20have%20never%20used%20Excel%20for%20more%20then%20less%20then%20basic%20things.%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20file%20that%20i%20was%20told%20to%20work%20on%20that%20is%20for%20many%20different%20address%20in%20the%20town%20I%20work%20for.%20I%20need%20to%20change%20some%20data%20in%20each%20and%20save%20the%20fie%20as%20that%20address.%20The%20cell%20for%20that%20address%20is%20A6%20on%20that%20work%20sheet.%20I%20would%20like%20to%20be%20able%20to%20change%20the%20address%20in%20that%20cell%20and%20hit%20the%20save%20button%20and%20it%20change%20the%20name%20and%20save%20that%20file%20to%20a%20designated%20spot%20on%20my%20computer.%20This%20way%20I%20can%20change%20the%20info%20and%20save%20as%20each%20time%20with%20out%20having%20to%20retype%20the%20new%20address%20again%20in%20the%20save%20as%20section.%20I%20will%20be%20doing%20this%20for%20almost%205000%20addresses%20so%20any%20way%20possible%20to%20stream%20line%20this%20would%20be%20awesome.%20Is%20this%20possible.%20I%20know%20it%20is%20done%20in%20the%20Macro%20section%20and%20I%20though%20that%20I%20had%20the%20right%20info%20entered%20but%20when%20I%20check%20to%20debug%20it%20I%20always%20get%20an%20error.%3C%2FP%3E%3CP%3EHere%20is%20the%20info%20I%20found%20and%20copy%20and%20pasted%20into%20the%20Macro%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESub%20FileNameAsCellContent()%20Dim%20FileName%20As%20String%20Dim%20Path%20As%20String%20Application.DisplayAlerts%20%3D%20False%20Path%20%3D%20%22C%3A%5Ctest%5C%22%20FileName%20%3D%20Range(%22A6%22).Value%20%26amp%3B%20%22.xlsx%22%20ActiveWorkbook.SaveAs%20Path%20%26amp%3B%20FileName%2C%20xlOpenXMLWorkbook%20Application.DisplayAlerts%20%3D%20True%20ActiveWorkbook.Close%20End%20Sub%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20error%20I%20keep%20getting%20is%26nbsp%3B%3C%2FP%3E%3CP%3ERun-time%20Error%201004%3C%2FP%3E%3CP%3EMethod%20'SaveAS'%20of%20object%20'%20_workbook'Failed%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20help%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1963137%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1964083%22%20slang%3D%22en-US%22%3ERe%3A%20Save%20as%20name%20change%20to%20cell%20info%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1964083%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F893571%22%20target%3D%22_blank%22%3E%40Jblake%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EDoes%20this%20work%20better%3F%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-visual%22%3E%3CCODE%3ESub%20FileNameAsCellContent()%0A%20%20%20%20Dim%20FileName%20As%20String%0A%20%20%20%20Dim%20Path%20As%20String%0A%20%20%20%20Application.DisplayAlerts%20%3D%20False%0A%20%20%20%20Path%20%3D%20%22C%3A%5Ctest%5C%22%0A%20%20%20%20FileName%20%3D%20Range(%22A6%22).Value%20%26amp%3B%20%22.xlsm%22%0A%20%20%20%20ActiveWorkbook.SaveAs%20Path%20%26amp%3B%20FileName%2C%20xlOpenXMLWorkbookMacroEnabled%0A%20%20%20%20Application.DisplayAlerts%20%3D%20True%0A%20%20%20%20ActiveWorkbook.Close%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1979769%22%20slang%3D%22en-US%22%3ERe%3A%20Save%20as%20name%20change%20to%20cell%20info%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1979769%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20unless%20I%20am%20doing%20something%20wrong%20in%20the%20macro%20it%20is%20not%20working.%20It%20didnt%20get%20any%20debug%20issues%20but%20when%20I%20exit%20and%20then%20I%20change%20the%20name%20is%20cell%20A6%20and%20click%20save%20as%20it%20doesn't%20change%20the%20name%20there%20automatically.%20I%20still%20have%20to%20change%20that%20name%20to%20match%20what%20is%20in%20cell%20A6.%20Not%20sure!!!!%3C%2FP%3E%3CP%3EThanks%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

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 

3 Replies

@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

@Hans Vogelaar 

 

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 

@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.