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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,444
Office Version
  1. 365
Platform
  1. Windows
What is highlighted when you get the compile error?
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,444
Office Version
  1. 365
Platform
  1. Windows
That works for me, try changing the names of your variables as previously suggested.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,910
Office Version
  1. 365
Platform
  1. Windows
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:

hautoguy2021

New Member
Joined
Jan 8, 2021
Messages
8
Office Version
  1. 365
Platform
  1. Windows
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?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,444
Office Version
  1. 365
Platform
  1. Windows
Did you try Joe's code? If so do you still get a compile error?
 

Watch MrExcel Video

Forum statistics

Threads
1,129,321
Messages
5,635,570
Members
416,865
Latest member
vipett

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
Top