Afternoon from a very Sunny South Africa,

I have a macro that copys a worksheet, puts it onto an email and then the
user sends the email to whom ever they need to.

Now the problem is when the receipant opens the mail and is asked update or
not, either way the cells show #value.

So now I want to change this macro to copy - paste special - vaules and
formats. When I put in the paste special and run the below copied macro, I
hit a snag.

So anybody with a simple solution?

Here is the macro:

Sub Mail_BVOMonthEnd()
Dim wb As Workbook
Application.ScreenUpdating = False
Cells.Select
ActiveSheet.Copy
Cells.Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Set wb = ActiveWorkbook
With wb
..SendMail "", _
ThisWorkbook.Names("Spreadsheet_Name").RefersToRan ge.Value
'Range ("E2") & " Payroll MonthEnd figures"
'"This is the Subject line"
..Close False
End With
Application.ScreenUpdating = True
End Sub

Thanks

I think the problem is in the worksheet.copy try this in your macro
you do not need to paste special formats, in that the formats are already
there in this sheet. if you are trying to select a different sheet, the
macro need to be revised.


Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

"Sunnyskies" wrote:

Afternoon from a very Sunny South Africa,

I have a macro that copys a worksheet, puts it onto an email and then the
user sends the email to whom ever they need to.

Now the problem is when the receipant opens the mail and is asked update or
not, either way the cells show #value.

So now I want to change this macro to copy - paste special - vaules and
formats. When I put in the paste special and run the below copied macro, I
hit a snag.

So anybody with a simple solution?

Here is the macro:

Sub Mail_BVOMonthEnd()
Dim wb As Workbook
Application.ScreenUpdating = False
Cells.Select
ActiveSheet.Copy
Cells.Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Set wb = ActiveWorkbook
With wb
.SendMail "", _
ThisWorkbook.Names("Spreadsheet_Name").RefersToRan ge.Value
'Range ("E2") & " Payroll MonthEnd figures"
'"This is the Subject line"
.Close False
End With
Application.ScreenUpdating = True
End Sub

Thanks

BJ, I give you permission to have the day off. Any problems phone BobM, he
will agree.

"bj" wrote:

I think the problem is in the worksheet.copy try this in your macro
you do not need to paste special formats, in that the formats are already
there in this sheet. if you are trying to select a different sheet, the
macro need to be revised.


Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

"Sunnyskies" wrote:

Afternoon from a very Sunny South Africa,

I have a macro that copys a worksheet, puts it onto an email and then the
user sends the email to whom ever they need to.

Now the problem is when the receipant opens the mail and is asked update or
not, either way the cells show #value.

So now I want to change this macro to copy - paste special - vaules and
formats. When I put in the paste special and run the below copied macro, I
hit a snag.

So anybody with a simple solution?

Here is the macro:

Sub Mail_BVOMonthEnd()
Dim wb As Workbook
Application.ScreenUpdating = False
Cells.Select
ActiveSheet.Copy
Cells.Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Set wb = ActiveWorkbook
With wb
.SendMail "", _
ThisWorkbook.Names("Spreadsheet_Name").RefersToRan ge.Value
'Range ("E2") & " Payroll MonthEnd figures"
'"This is the Subject line"
.Close False
End With
Application.ScreenUpdating = True
End Sub

Thanks