Dec 07 2020 07:55 AM - edited Dec 07 2020 08:06 AM
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
Dec 07 2020 11:13 AM
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
Dec 11 2020 06:04 AM
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
Dec 11 2020 06:49 AM
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.