FilenameAsCell

hautoguy2021

New Member
Joined
Jan 8, 2021
Messages
8
Office Version
  1. 365
Platform
  1. Windows
I get "compile Error" "Syntax Error" when i run the macro in the worksheet. Any help here?

VBA Code:
Sub FileNameAsCellContent()
Dim FileName As String
Dim Path As String
Application.DisplayAlerts = False
Path = "C:\test\"
FileName = Range("E3").Value & ".xlsx"
ActiveWorkbook.SaveAs Path&FileName,xlOpenXMLWorkbook
Application.DisplayAlerts = True
ActiveWorkbook.Close
End Sub
 
What is highlighted when you get the compile error?
 
Upvote 0

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
That works for me, try changing the names of your variables as previously suggested.
 
Upvote 0
ActiveWorkbook.SaveAs Path & FileName, xlOpenXMLWorkbook
and had space on both side of &
Do you actually have a "C:\Test" folder on your computer?
Do you have rights to save to it?

See if this works any better for you:
VBA Code:
Sub FileNameAsCellContent()

    Dim fName As String
    Dim fPath As String

    Application.DisplayAlerts = False
  
    fPath = "C:\test\"
    fName = Range("E3").Value & ".xlsx"
    ActiveWorkbook.SaveAs fPath & fName, xlOpenXMLWorkbook

    Application.DisplayAlerts = True

    ActiveWorkbook.Close

End Sub

If that doesn't work, try temporarily removing this line:
VBA Code:
Application.DisplayAlerts = False
and see if it gives you any more information/warnings.


EDIT: I missed that Fluff suggested changing the variable names, which is essentially what my code above does.
 
Last edited:
Upvote 0
I think I have mislead everyone here.
I want to save a spreadsheet I call "customer quotes", to a file called "Quotes". I enter the customer name in cell E3.
I would like the Macro to save the sheet (workbook) to the quotes file, named the name in E3.
Does that help to clear up my goal?
 
Upvote 0
Did you try Joe's code? If so do you still get a compile error?
 
Upvote 0

Forum statistics

Threads
1,212,927
Messages
6,110,728
Members
448,294
Latest member
jmjmjmjmjmjm

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top