Button to copy and e-mail out worksheet with added functionality

I have a button with some simple code that copies Sheet2 and opens it in a new workbook:

Please Login or Register  to view this content.

Two extra things I'd like
1) Would it be possible to e-mail this sheet out when opened? If the functionality of "Would you like to e-mail this sheet out?" popped up as a Yes/No message box then even better. If no is selected then they can just view the sheet. If yes is selected an Outlook message window opens with the file attached?

2) I currently get a "Subscript out of range" error when the Sheet2 opens, I think this may be because I am only copying Sheet2, but within the code of Sheet2 it looks at other sheets...for example the part in red below, it is taking to a sheet called "Archive":

Please Login or Register  to view this content.

Is there a way to lose all relationships between other sheets when the copy of Sheet2 is created? I only wish for it to be a 'snapshot' and am not bothered about the main code functionality from the master file coming across...

Thanks in advance.

Great, thank you for this.

I have used the "Mail one sheet" option to mail and it seems to work great

Just one point:

Is there any way to keep the formatting of Sheet2 when it is copied? What you have posted loses the code which is great but loses the formatting also and I'd like to keep this if possible (apologies I should have said in my OP).

Thanks!

Can anyone assist with my post #3 above?

I read that xlPasteFormats could work? So I tried

Please Login or Register  to view this content.

but this didn't seem to work... any ideas?

thanks

Is anybody able to help with the above? All I'd like to add is some code which ensures the formatting is kept when the new sheet is created

Here is the foundation of what you need. You'll need to change sheet name and/or references:

Sub CopyWorksheetValues()
ActiveSheet.Copy
Cells.Copy
Range("A1").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End Sub

You want to copy Sheet2 - only the data - no formulas ... correct ?

Where are you pasting this to ? What is the name of the sheet ?

Is it in the same workbook or in another workbook ?

Only wish to copy Sheet2 values, correct.
Pasting to a new workbook which will open as just Sheet2.

Basically, everything in the above code worked perfect, I just do not wish to reference any other sheets, such as the Archive sheet in Post #1 that is causing the error. Thanks