Copy a formula free excel file

Guys

Can you help I need a smalll change to this formual to only copy and save a specific worksheet in the workbook the sheet is named DAYWORK.
The code attached copies the entire workbook and creates a file with NO formula only values which is what I want but only need it to copy the 1 worksheet named DAYWORK

can you help please

Darren
Sub MakeValuesAndSaveAs()

    Dim ws As Worksheet

    For Each ws In ThisWorkbook.Worksheets
        ws.Cells.Copy
        ws.Cells(1, 1).PasteSpecial xlPasteValues
    Next

    ThisWorkbook.SaveAs ThisWorkbook.Path & "\" & "foo " & ThisWorkbook.Name

    MsgBox "Done"

End Sub

Open in new window

DarrenJacksonAsked:
Who is Participating?
 
Saqib Husain, SyedEngineerCommented:
Try this modification

Sub MakeValuesAndSave1WsAs()

    Dim ws As Worksheet

    set ws =ThisWorkbook.Worksheets("DAYWORK")
        ws.Cells.Copy
        ws.Cells(1, 1).PasteSpecial xlPasteValues

    ThisWorkbook.SaveAs ThisWorkbook.Path & "\" & "foo " & ThisWorkbook.Name

    MsgBox "Done"

End Sub
0
 
jppintoCommented:
Try like this...

jppinto
Sub MakeValuesAndSaveAs()

    Dim ws As Worksheet
    Set ws=Sheets("DAYWORK")
    ws.Cells.Copy
    ws.Cells(1, 1).PasteSpecial xlPasteValues
    
    ThisWorkbook.SaveAs ThisWorkbook.Path & "\" & "foo " & ThisWorkbook.Name

    MsgBox "Done"

End Sub

Open in new window

0
 
DarrenJacksonAuthor Commented:
Wow that was quick cheers guys I will split as you both helped thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.