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

New Member
Joined
Sep 1, 2021
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
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.
VBA Code:
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):
VBA Code:
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
 
Last edited by a moderator:

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,938
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
Which cell on the cost template sheet has the name of the file?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,938
Office Version
  1. 365
Platform
  1. Windows
Ok, how about
VBA Code:
    ActiveWorkbook.SaveAs Filename:= _
        "Q:\abc\Folder 1\Folder 2\Folder 3\" & Range("C4").Value & ".xlsm", FileFormat:= _
        xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
 
Last edited:
Solution

vcravatta

New Member
Joined
Sep 1, 2021
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
Perfect! Thank you so much!
 
Last edited by a moderator:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,938
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Forum statistics

Threads
1,148,170
Messages
5,745,169
Members
423,930
Latest member
Simple77

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