macro for copying a worksheet to a new book with celled valued-out, then "file save as new name" on the new WB where the new name is referenced from a

[vcravatta]

I found two macros I've been trying to combine, and it's almost working. I had the following to create the valued-out new workbook, but I wanted to also add the file name being driven by a cell on the worksheet.

Sub recipedevelopmentformoutput()
'
' recipedevelopmentformoutput Macro
'

'
    Sheets("Cost Template").Select
    Sheets("Cost Template").Copy
    Cells.Select
    Range("T1").Activate
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    ChDir "Q:\abc\Folder 1\Folder 2\Folder 3"
    ActiveWorkbook.SaveAs Filename:= _
        "Q:\abc\Folder 1\Folder 2\Folder 3\afile.xlsm", FileFormat:= _
        xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
End Sub

I also found the following macro, which is working to create a new file, but it's the entire workbook (lots of sheets and formulas):

Public Sub Tester()
    Dim WB As Workbook
    Dim SH As Worksheet
    Dim Rng As Range
    Dim sStr As String
    Const mySheet As String = "Cost Template"
    Const myCell As String = "C4"

    Set WB = ActiveWorkbook
    Set SH = WB.Sheets(mySheet)
    Set Rng = SH.Range(myCell)
    sStr = Rng.Value

    With WB
        .Save
        .SaveAs Filename:=.Path & Application.PathSeparator & sStr, _
                FileFormat:=.FileFormat
    End With
End Sub

I tried combining the two and *almost* got it to work, but i get an error that I can't save it with the VBA project/macros, even though the original file is .xlsm. I also tried to edit the second macro to try to force it to save the new WB as .xlsm, but I can't get it to work.

Also asked here macro for copying a worksheet to a new book with celled valued-out, then "file save as new name" on the new WB where the new name is referenced from a specific cell in workbook

 

[Fluff]

Hi & welcome to MrExcel.
Which cell on the cost template sheet has the name of the file?

 

[vcravatta]

Hi & welcome to MrExcel.
Which cell on the cost template sheet has the name of the file?

It is C4

 

[Fluff]

Ok, how about

    ActiveWorkbook.SaveAs Filename:= _
        "Q:\abc\Folder 1\Folder 2\Folder 3\" & Range("C4").Value & ".xlsm", FileFormat:= _
        xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False

 

[vcravatta]

Perfect! Thank you so much!

 

[Fluff]

You're welcome & thanks for the feedback.