Copy range to new workbook w/some cells as values & some cells as formulas in new workbook

Hello, I am a beginning VBA user and this is my first post in this forum. I am copying a range from a worksheet into a new workbook, and then sending the new workbook as an email attachment. The email part works okay, but I need assistance on the copy portion.

In my old worksheet I have some formulas in my range that I am copying, and I want to paste some of the formulas as "values" into my new workbook, but I also want to paste certain formulas as "formulas" into the new workbook. So far I can only get my macro to copy values OR formulas.

Also, my new workbook is not keeping my page setup (one page) from my old workbook; it is creating 4 pages. I've searched for a solution but have not found an answer to fix this. I want the manager to open the attachment and view/print only one page without having to adjust the page setup themselves.

Here is the code and I also attached my workbook (I found this code in the book "101 Ready to Use Excel Macros" by M.Alexander & J.Walkenbach):

Please Login or Register  to view this content.

Try this. Put the code in a standard code module (e.g.; Module2) and assign the macro button to this code. Delete your duplicate code in the ThisWorkbook code module.

Please Login or Register  to view this content.

You're welcome. Mark this thread as solved.

Also, add this line to delete the button from the worksheet copy.

Please Login or Register  to view this content.