Copy sheet to new workbook with no formulas

Guys

I have already had a response to a post on here today but the customer has changed his mind (shock horror) :)

I need to be able to copy the contents of a worksheet named (DAYWORK) to an new workbook but just the VALUES no formulas.

Can you help

Darren
DarrenJacksonAsked:
Who is Participating?
 
jppintoConnect With a Mentor Commented:
Here you go...
Sub MakeValuesAndSaveAs()

    Dim ws As Worksheet
    Dim ws_target as Worksheet
    Set ws=Sheets("DAYWORK")
    ws.Cells.Copy
    Workbooks.Add
    ActiveSheet.Name = "DAYWORK_TARGET"
    Set ws_target=Sheets("DAYWORK_TARGET")
    ws_target.Cells(1, 1).PasteSpecial xlPasteValues
    ws_target.Cells(1, 1).PasteSpecial xlPasteFormats
    ThisWorkbook.SaveAs ThisWorkbook.Path & "\" & "foo " & ThisWorkbook.Name

    MsgBox "Done"

End Sub

Open in new window

0
 
sir plusSales ManagementCommented:
Click in the square between th ecolumn headeres & the row headers to select the whole sheet oon th esource sheet
Push ctrl & C or otherwise copy by your favorite method
Click in the square between th ecolumn headeres & the row headers to select the whole sheet on the target sheet
Right click on the selected cells and choose "Paste Special|Values|Okay"
You are done
Oh
Double click on the sheet tab and rename it
0
 
jppintoCommented:
I think that this might work...

jppinto
Sub MakeValuesAndSaveAs()

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

    MsgBox "Done"

End Sub

Open in new window

0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
DarrenJacksonAuthor Commented:
Sorry should have been more specific

I need it automated in vb my bad sorry
0
 
DarrenJacksonAuthor Commented:
jppinto

is there anyway I can keep the formatting dont need formuals just values n formatting

thanks

I know Im a pain :)
0
 
DarrenJacksonAuthor Commented:
Great Thanks works a treat
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.