Help adding Copy PasteSpeical to VBA

[ahazeldene]

Hi,

I have the following code, which aims to save my workbook as an xlsx file and then email to a colleague. The problem is that it corrupts the data in the sheet, so I would like to add a PasteSpecial Paste:=xlPasteValuesAndNumberFormats argument.

Current code:

Please Login or Register  to view this content.

If someone can help me copy the contents of the ActiveSheet and Paste Special numbers and formats, before the email is sent, that would be great.

Thanks,

[AlphaFrog]

I think all you may need is to add this to convert the formulas to values before saving.

Please Login or Register  to view this content.

[ahazeldene]

Hi,

This still leave me with errors unfortunately. The worksheet before running the macro looks like this:

2017-04-12 08_14_27-Abdur Morning Update - Excel.png

Once emailed and opened like this:

2017-04-12 08_14_45-Morning Update 12-Apr-17 [Read-Only] - Excel.png

I think therefore that I need the code to PasteValuesAndNumberFormats, then send with only the values and formats, no underlying codes.

Is this possible to achieve?

Thanks

[JOHN H. DAVIS]

Try:

Please Login or Register  to view this content.

[ahazeldene]

Hi,

The code gets stuck on:

2017-04-17 16_31_49-Microsoft Visual Basic for Applications - Abdur Morning Update.xlsm [break] .png

Do you know how I can modify? I been struggling to get this to work for a while.

Appreciate the help,

[bakerman2]

Please Login or Register  to view this content.

[ahazeldene]

Hi,

It now causes Excel to crash and close:

Please Login or Register  to view this content.

Any ideas why?

I would just like to the code to copy and paste the values and then email the workbook.

Any ideas?
Thanks

[humdingaling]

you should put the loop after you do the copy (like in alphafrogs' code)
otherwise your main spreadsheet will be hard coded with values and formulas lost (if there are any)

[humdingaling]

looking at the picture
how is your spreadsheet update? do you have a data connection of some kind?