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.
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
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.
 
Upvote 0
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:
Upvote 0
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?
 
Upvote 0
Try to remove " & .xlsm" from this line (FileName = Range("B60").Value & ".xlsm")
 
Upvote 0

Forum statistics

Threads
1,212,938
Messages
6,110,782
Members
448,297
Latest member
carmadgar

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