[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 198
  • Last Modified:

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

0
DarrenJackson
Asked:
DarrenJackson
2 Solutions
 
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now