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

recreated1

New Member
Joined
Dec 3, 2004
Messages
11
Office Version
  1. 2016
Platform
  1. Windows
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!!

Rich (BB code):
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
 
Last edited by a moderator:

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,314
Office Version
  1. 365
Platform
  1. Windows
What exactly is sname when you get the error?
 

recreated1

New Member
Joined
Dec 3, 2004
Messages
11
Office Version
  1. 2016
Platform
  1. Windows
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??
 

recreated1

New Member
Joined
Dec 3, 2004
Messages
11
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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

recreated1

New Member
Joined
Dec 3, 2004
Messages
11
Office Version
  1. 2016
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,314
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Glad you sorted it & thanks for the feedback.
 

recreated1

New Member
Joined
Dec 3, 2004
Messages
11
Office Version
  1. 2016
Platform
  1. Windows
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

New Member
Joined
Dec 4, 2020
Messages
8
Office Version
  1. 2016
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,314
Office Version
  1. 365
Platform
  1. Windows
@lesleyfayburton
Please do not "hijack" another members thread. You need to start a thread of your own. Thanks.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,359
Messages
5,601,153
Members
414,431
Latest member
JustmemyselfandI

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