asked on

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

sir plus

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

jppinto

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

DarrenJackson

ASKER

Sorry should have been more specific

I need it automated in vb my bad sorry

DarrenJackson

ASKER

jppinto

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

thanks

I know Im a pain :)

ASKER CERTIFIED SOLUTION

jppinto

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

DarrenJackson

ASKER

Great Thanks works a treat