vba to save as new workbook in current path using cell value as the new file name

[recreated1]

Hi,
I am a bit of a novice and have been trying to piece together a macro using code posted in the forum.
I am using Excel 2016
I am trying to create a save button that will save a new copy of the open workbook in the same directory path as the original, using the value in the first worksheet, Cell B2, as the new file name.
I have the following code but get a runtime error 1004 [Method 'SaveAs' of object'_Workbook' failed] on the red highlighted line.
Additionally, the original document contains the Macro so its an .xlsm format, and in the new document I want to disable the macro so saving as an .xlsx format
Can someone help me figure this out?
Thanks!!

Sub SaveAsNewFile()


    Dim relativePath As String, sname As String
    sname = ActiveWorkbook.Worksheets(Sheet1).Range("B2") & ".xlsx"
    relativePath = Application.ActiveWorkbook.Path & "\" & sname
    Application.DisplayAlerts = False
    ActiveWorkbook.CheckCompatibility = False
    ActiveWorkbook.SaveAs FileName:=relativePath, FileFormat:=51
    Application.DisplayAlerts = True
End Sub

 

[Fluff]

What exactly is sname when you get the error?

 

[recreated1]

The error message does not indicate the sname. It reads exactly as I put it above. The original document name is "2021 Dealer Planning Template" but the sname will change based on the contents of Sheet1, Cell B2. Does that make sense??

 

[Fluff]

Does that make sense??

Yes, but I still need to know exactly what sname was when you got the error.

 

[recreated1]

Ah OK - for the test the sname would be "Corporate Environments of Georgia"

 

[recreated1]

OK I figured it out. There is a hidden sheet called Sheet1. The actual sheet I needed to pull the cell name from was called something different.

 

[Fluff]

Glad you sorted it & thanks for the feedback.

 

[recreated1]

OK I have a new problem - when I run the macro, the file saves as I want it to but it also overwrites the original Template file. Below is the current VBA code:

Sub SaveAsNewFile()


Dim relativePath As String, sname As String
sname = ActiveWorkbook.Worksheets("Observations and Trends").Range("B2") & ".xlsx"
relativePath = Application.ActiveWorkbook.Path & "\" & sname
Application.DisplayAlerts = False
ActiveWorkbook.CheckCompatibility = False
ActiveWorkbook.SaveAs FileName:=relativePath, FileFormat:=51
Application.DisplayAlerts = True

End Sub

The Template file is called "2021 Planning Template"
When it is opened the user will enter a numerical value specific to a client in Cell B2 in the Sheet titled "Observations and Trends" . This then populates several fields in the form with data specific to that client. The macro and source data file is in the template but hidden to the user.
After the form has been fully completed with additional information, the user clicks on the "Save As New File" button which runs the above macro.
The problem I am having is that after the Macro runs, the new file saves properly but remains open and the Template file is now overwritten and the embedded marcos are no longer associated with the template. I would like for the new file to save and close and then have the Template file reopen in the original blank .xlsm format
How can I work around this to get the proper functionality?

 

[lesleyfayburton]

Glad you sorted it & thanks for the feedback.

This newbie desperately needs help. I have spent a MONTH on this and can't get it to work. It should be so simple.
Exporting an excel range of cells to pdf and saving that file based on cells in that worksheet. I am using the range and I can not get the set rng to work. I also can't get it to save to a location. I don't want it to be saved in the same location as the worksheet. Also I need to save it and have a prompt not to overwrite. I keep using the same codes that everyone seems to be using and nothing is working. I have got to finish this tomorrow or I will lose it.

ub Clickticketsave1b1s()

Dim Path As String
Dim fName As String
Dim rng As Range
Set rng = Sheets("Trade Ticket").Range("A1:G43").Value
filename = Workbooks("Disaster Recovery Trade Ticket - 2020").Sheets("Trade Ticket").Range("A1:G43").Value
SaveAsStr = filename & ".pdf"
rng.ExportAsFixedFormat Type:=TypePDF, filename:="T:/Department/Investment Sales/_Restricted/TRADING/TRADE TICKETS/pdf/" & fName, Quality:=xlQualityStandard, IncludeDocProperties:=False, IgnorePrintAreas:=False, OpenAfterPublish:=True

Set wbA = ActiveWorkbook
Set wsA = ActiveSheet
saveLocation = "T:/Department/Investment Sales/_Restricted/TRADING/TRADE TICKETS/"


Dim EmailSubject As String, EmailSignature As String
Dim PDFFile As String
Dim Email_To As String, Email_CC As String, Email_BCC As String
Dim DisplayEmail As Boolean
Dim OutlookApp As Object, OutlookMail As Object

Set OlApp = CreateObject("Outlook.Application")
Set NewMail = OlApp.CreateItem(0)

On Error Resume Next
With NewMail
.to = ""
.CC = " "
.BCC = ""
.Subject = filename
.Body = " "
.Attachments.Add PDFFile
.Attachments.Add "T:\Department\Investment Sales\_Restricted\TRADING\TRADE TICKETS\" & Sheets("Trade Ticket").Range("R1").Value '--- full path of the pdf where it is saved

This website is amazing so I am hoping someone can have pity on me
.Display 'or use .Display to show you the email before sending it.
End With
On Error GoTo 0

End Sub

 

[Fluff]

@lesleyfayburton
Please do not "hijack" another members thread. You need to start a thread of your own. Thanks.