Save file on close with cell value in name

[mdjks]

I have an Excel sheet to collect data from various users. There is code in the workbook to email the file back to me. I want to have the file save using the value of a specific cell (Location number) prior to being sent.

I get Runtime error 1004 saying the couldn't be accessed with the code below. The error asks if the folder exists, readonly, that the name contains no special characters, etc.

If I change the file name and type it out it will save and doesn't need the full path. I run into the problem when I try to use the Lst variable. I had the same problem when I separated the Range Value and file name and tried Lst & Fle as the name. Fle = "_IssuesForm.xls".

I feel like I'm really close but missing something, Thanks in advance for your help!

Dim Lst As String
    Lst = Range("G19").Value & "_IssuesForm.xls"
    
    ActiveWorkbook.SaveAs Filename:= _
        Lst _
        , FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
        ReadOnlyRecommended:=False, CreateBackup:=False

 

[HaHoBe]

Hi, mdjks,

as the code will to be in a saved workbook you could try:

With Sheets("Sheet1").Range("G19")
  If .Value = "" Then
    MsgBox "Fill in missing value in Cell G19 on Sheet1", vbExclamation, "Can´t save"
    Exit Sub
  End If
  ActiveWorkbook.SaveAs FileName:=ThisWorkbook.Path & "\" _
    & .Value & "_IssuesForm"
End With

Ciao,
Holger

 

[Sergeant]

Dim Lst As String
    Lst = Range("G19").Value & "_IssuesForm.xls"
    
    ActiveWorkbook.SaveAs Filename:= _
        Lst _
        , FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
        ReadOnlyRecommended:=False, CreateBackup:=False

1. Does G19 contain a full path and prefix? ("C:\Folder\SubFolder\[FileNamePrefix]")

2. Try commenting out the 'ActiveWorkbook.SaveAs.... line and put in MsgBox Lst ...make sure it comes out as a fully qualified path and filename.

 

[mdjks]

Thank you for your help, it works nicely!