Copy a worksheet to new workbook and name the new workbook

[rammergu]

Hello,

I'm using Excel 2013.

I'm hoping you can help me achieve the following outcome using macro:

1) Copy current worksheet and create a new workbook.
2) this newly created workbook much be identical to the source worksheet, except there should be no formulae (content and formatting identical to source worksheet a).
3) in the source worksheet, cell A1 is date and cell B1 is customer name. I want the newly created workbook to be saved to my desktop using the content of cells A1 and B1 with an underscore separating the two strings.
eg: 20171202_JohnWest.xlsx

appreciate your kind assistance with this query and thank you in advance.

best regards,
ram

 

[Misca]

Try

Sub SaveAsNewWB()    
Dim MyPath As String
Dim wbName As String
    
MyPath = Environ("USERPROFILE") & "\Desktop\"
    
With ActiveSheet
    wbName = .Cells(1, 1).Value & "_" & .Cells(1, 2).Value
    .Copy
End With


With ActiveWorkbook
    With .Sheets(1).UsedRange.Cells
        .Value = .Value
    End With


    .SaveAs (MyPath & wbName & ".xlsx")
End With


End Sub

 

[rammergu]

Thank you Misca. This works as desired. sincerely appreciate it.

Try

Sub SaveAsNewWB()    
Dim MyPath As String
Dim wbName As String
    
MyPath = Environ("USERPROFILE") & "\Desktop\"
    
With ActiveSheet
    wbName = .Cells(1, 1).Value & "_" & .Cells(1, 2).Value
    .Copy
End With


With ActiveWorkbook
    With .Sheets(1).UsedRange.Cells
        .Value = .Value
    End With


    .SaveAs (MyPath & wbName & ".xlsx")
End With


End Sub