• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 206
  • Last Modified:

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
0
DarrenJackson
Asked:
DarrenJackson
  • 3
  • 2
1 Solution
 
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
 
DarrenJacksonAuthor Commented:
Sorry should have been more specific

I need it automated in vb my bad sorry
0
Industry Leaders: 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!

 
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
 
jppintoCommented:
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
 
DarrenJacksonAuthor Commented:
Great Thanks works a treat
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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