Create new workbook named for Cell in original workbook and copy some sheets from original

Good Day Experts

I need a bit of help with my code please.

I have a workbook that will serve as a master workbook which i need to have a button in that the user can press to create a new workbook with name based on

Sheets(("NEW MASTER ORDER FORM").Range("F2").value

Sheets("NEW MASTER ORDER FORM") and Sheets("Item Qty Pivot") and Sheets("Employee Deduction Pivot")

needs to be created in the new workbook with all data from the original workbook in, but formulas removed.

The code i have below(modified from code found on the web....thanks to the original creator if you see this ), does all that, but the sheets created in the new workbook comes out blank. What am i missing?

Sub MySheetCopy()
ScreenUpdating = False


    Dim mySourceWB As Workbook
    Dim mySourceSheet As Worksheet
    Dim myDestWB As Workbook
    Dim myNewFileName As String
    
'   First capture current workbook and worksheet
    Set mySourceWB = ActiveWorkbook
    Set mySourceSheet = ActiveSheet


'   Build new file name based
    myNewFileName = mySourceWB.Path & "\" & ActiveSheet.Range("F2").Value & ".xlsx"


'   Add new workbook and save with name of sheet from other file
    Workbooks.Add
    ActiveWorkbook.SaveAs Filename:=myNewFileName
    Set myDestWB = ActiveWorkbook
    
'   Copy over sheet from previous file


    mySourceWB.Activate
    Sheets("NEW MASTER ORDER FORM").Select
    myDestWB.Activate
    ActiveSheet.Copy After:=Worksheets(Sheets.Count)
    On Error Resume Next
    ActiveSheet.Name = "NEW MASTER ORDER FORM"

    mySourceWB.Activate
    Sheets("Item Qty Pivot").Select
    myDestWB.Activate
    ActiveSheet.Copy After:=Worksheets(Sheets.Count)
    On Error Resume Next
    ActiveSheet.Name = "Item Qty Pivot"
    
    mySourceWB.Activate
    Sheets("Employee Deduction Pivot").Select
    myDestWB.Activate
    ActiveSheet.Copy After:=Worksheets(Sheets.Count)
    On Error Resume Next
    ActiveSheet.Name = "Employee Deduction Pivot"
    
    Application.CutCopyMode = False
    
'   Resave new workbook
    ActiveWorkbook.Save


End Sub

… as a starter :

PHP Code: 

Sub Demo()
        Dim Ws As Worksheet
        Application.ScreenUpdating = False
        Worksheets([{"NEW MASTER ORDER FORM","Item Qty Pivot","Employee Deduction Pivot"}]).Copy
    With ActiveWorkbook
        For Each Ws In .Worksheets
            With Ws.UsedRange
                .Formula = .Value
            End With
        Next
            .SaveAs ThisWorkbook.Path & Application.PathSeparator & .Sheets("NEW MASTER ORDER FORM").[F2].Text
    End With
        Application.ScreenUpdating = True
End Sub 


Do you like it ? So thanks to click on bottom left star icon « ★ Add Reputation » !

Works like a bomb.

Thank you very much Mark.

Originally Posted by ANDREAAS

Thank you very much Marc.

Thanks for the rep' !

thank you...very helpful