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

[happydayze]

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.

[AlphaFrog]

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.

[happydayze]

Dear Alpha,

Thank you! Thank you! It works perfectly! This will help me tremendously as I will have over 170 records to perform this macro on. I really appreciate your help and expertise.

Happy

[AlphaFrog]

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.