SaveAs using Cell Reference - Macro

KickingitCrazy

New Member
Joined
Jan 1, 2021
Messages
2
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
I want to the workbook to SaveAs (1) a specific value in cell (2) allow user to change where the workbook is saved and (3) without closing the workbook. I'm very new to using Macros, I just Google codes and use them.

I keep getting an error on line 7 "ActiveWorkbook.SaveAs FileName:=Path & FileName, FileFormat:=xlOpenXMLWorkbookMacroEnabled" I'm not sure what to do about this error, I've tried a couple different things but no success. I'm sure the error is above my knowledge level. If you could also provide some direction on how to resolve my (2) and (3) issues, I'd appreciate.

Code:
Sub FileNameAsCellContent()
Dim FileName As String
Dim Path As String
Application.DisplayAlerts = False
Path = "C:\Users\Username\"
FileName = Range("B60").Value & ".xlsm"
ActiveWorkbook.SaveAs FileName:=Path & FileName, FileFormat:=xlOpenXMLWorkbookMacroEnabled
Application.DisplayAlerts = True
ActiveWorkbook.Close
End Sub

I already have two other macros on the sheet as buttons, spellcheck and email. Those both work fine.
 

Some videos you may like

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.

EXCEL MAX

Active Member
Joined
Nov 11, 2020
Messages
379
Office Version
  1. 2007
Platform
  1. Windows
Hello Kickin Crazy,
if you testing only this part of code,
try first to save active workbook manually as macro enabled file.
Now when workbook is saved try to run code to make copy of the workbook.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,360
Office Version
  1. 365
Platform
  1. Windows
Welcome to the MrExcel board!

When posting vba code, please use code tags. My signature block below has more details.

I keep getting an error
When you are asking about a vba error it always helps to give the full error message (as well as the problem line which you did)
 
Last edited:

KickingitCrazy

New Member
Joined
Jan 1, 2021
Messages
2
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
I saved the workbook and now I can't open it, I didn't SaveAs though : (

This is message I receive:

"Excel cannot open the file because the file format or file extension is not valid. Verify that the file has not been corrupted and that the file extension matches the format of the file."

What can I do?
 

EXCEL MAX

Active Member
Joined
Nov 11, 2020
Messages
379
Office Version
  1. 2007
Platform
  1. Windows
Try to remove " & .xlsm" from this line (FileName = Range("B60").Value & ".xlsm")
 

Watch MrExcel Video

Forum statistics

Threads
1,126,957
Messages
5,621,823
Members
415,859
Latest member
Vain

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