Copy a worksheet range to a new worksheet, keeping formatting and values, but not formulas

Hello

I have a workbook with ten worksheets. I am trying to copy just one worksheet (an estimate) across to a new file, named for that invoice number (field F10). I want to copy just the range A1 to F61, and include the range's formatting and values, but not its formulas. The following code partially enables me to do this. However, it is also copying all the other worksheets in the workbook to the new file at the same time, which I don't want. I tried numerous variations, but can't get it to copy just the one estimate worksheet. Is there a way of fixing this code please?

Sub SaveEstimateToNewWorkbook()
    Dim NewFN As Variant
    ' Copy file to a new workbook
    
    ActiveSheet.Range("A1:F61").Copy
    ActiveSheet.Range("A1:F61").PasteSpecial xlPasteValues
    ActiveSheet.Range("A1:F61").PasteSpecial xlPasteFormats

    NewFN = "C:\Users\MyFiles\Documents\Database\Invoices\Estimate - " & Range("F10").Value & ".xlsx"
    ActiveSheet.SaveAs NewFN, FileFormat:=xlOpenXMLWorkbook
         
End Sub

I have seen many queries like this on the forum, but just can't find the right answer for my needs. I would be grateful for any suggestions.

Thank you
Terri H

Hi Terri,

See if this code helps

Sub CreateCopy()

Dim txtDir As String
Dim txtExt As String
Dim txtFileName As String

Application.ScreenUpdating = False

    txtDir = "C:\Users\MyFiles\Documents\Database\Invoices\"
    txtExt = ".xlsx"
    txtFileName = txtDir & "Estimate - " & Range("F10").Value & txtExt
    
    'Copy Active Sheet to a new workbook
    ActiveSheet.Copy
    
    'copy and paste only the values
    Range("A1:F61").Value = Range("A1:F61").Value
    
    'save the new workbook with the desired name
    ActiveWorkbook.SaveAs FileName:=txtFileName
    ActiveWorkbook.Close
    
    'display a message box
    MsgBox "The Estimate " & Range("F10").Value & " was saved in the following dir:" & vbNewLine & _
            txtDir, vbInformation + vbOKOnly, "Saving complete"            
    
Application.ScreenUpdating = True

End Sub

Hi FredLo 2010

That seems to be working perfectly! Thank you.

Terri H

You are very Welcome I am glad I was able to help