copy all sheets to a new workbook

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

futbol - 15 Nov 2007 17:16 GMT

hello gurus,
i'm looking for vba code to copy only values and formats to a new
workbook preserving the name on each tab.
thanks in advance

Reply to this Message

JP - 15 Nov 2007 18:39 GMT

There's some code on this page that can assist you.

http://www.rondebruin.nl/values.htm

You can use the code in "Change all the cells in all worksheets to
values", save the file with a new name, and close the original
workbook without saving. This should do what you want.

HTH,
JP

> hello gurus,
> i'm looking for vba code to copy only values and formats to a new
> workbook preserving the name on each tab.
> thanks in advance

Reply to this Message

futbol - 16 Nov 2007 01:28 GMT

> There's some code on this page that can assist you.
>
[quoted text clipped - 13 lines]
>
> - Show quoted text -

it does work, obviously including code to finish the job saving the
workbook with values or creating another one would be nice

Reply to this Message

JP - 16 Nov 2007 12:09 GMT

At the beginning of the macro, put

Dim AWS as worksheet
Set AWS = Activesheet

At the end, put

Activeworkbook.SaveAs "your new values-only workbook filename here"
AWS.close false

(note untested code)
This will set a reference to the original workbook, save the new file
to a new name, then close the original workbook so you can reuse.

HTH,
JP

> it does work, obviously including code to finish the job saving the
> workbook with values or creating another one would be nice- Hide quoted text -
>
> - Show quoted text -

Reply to this Message