VBA Saving a file using a filename as a specified cell and as a CSV file

[khouston]

Hi
i am trying to create a macro that will automatically save a sheet as a CSV sheet with the filename as a concatenated cell name within another sheet if that makes sense.
this is what i have so far.

sfilename = Sheets("dealer input").Range("i1").Value

Sheets("CSV").Select.SaveAs Filename:= _
"X:\Applications\1-Inbound\sfilename.csv", FileFormat:=xlCSV, _
CreateBackup:=False

End Sub

but all this is doing is saving the file as sfilename
any help will be greatly appreciated
thanks

 

[cornflakegirl]

Try

Sheets("CSV").Select.SaveAs Filename:= _
"X:\Applications\1-Inbound\" & sfilename & ".csv", FileFormat:=xlCSV, _
CreateBackup:=False

 

[khouston]

Hi Emma

thanks for that.
it is still coming up with a Debug error on the file location

"X:\Applications\1-Inbound\" & sfilename & ".csv", FileFormat:=xlCSV, CreateBackup:=False

its not liking this part for some reason.

 

[cornflakegirl]

Sorry, didn't look at your code properly - I think the problem might be with trying to apply SaveAs to a single worksheet.

 

[dmt32]

Hi,
see if this helps:

     Sheets("CSV").Copy
    With ActiveWorkbook
        .SaveAs Filename:= _
                "X:\Applications\1-Inbound\" & sfilename & ".csv", _
                FileFormat:=xlCSV, _
                CreateBackup:=False
        .Close False
    End With

Dave

 

[khouston]

yeah this works perfectly, thank you very much for your help both.