Why does this Macro not work - cut, copy and paste

[Guest]

Morning from a overcast South Africa,

I have this macro that is supposed to copy a worksheet, paste the values and
formats onto a new worksheet and then send the new worksheet via e-mail.

Can you please identify why when copy and pasted onto the new worksheet ,the
calculated cells all have #REF!.

Here is the macro that is causing the problem.

Sub EMail()
'Sub UnprotectAllSheets()
Application.ScreenUpdating = False
Dim n As Single
For n = 1 To Sheets.Count
Sheets(n).Unprotect Password:="Drivers"
Next n
Application.ScreenUpdating = True
'End Sub

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

'Sub ProtectAllSheets()
Application.ScreenUpdating = False
'Dim n As Single
For n = 1 To Sheets.Count
Sheets(n).Protect Password:="Drivers"
Next n
Application.ScreenUpdating = True
End Sub

Waiting in anticipation.

 

[Pete_UK]

After your paste special | values line, I think you will need this:

Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, _
SkipBlanks:= False, Transpose:=False
Application.CutCopyMode = False

Hope this helps.

Pete

 

[Guest]

Afternoon Pete,

I inserted the script you provided between the lines:
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
and

Set wb = ActiveWorkbook

And unfortunately still get the #REF! on the new worksheet once I had opened
it up.

Any other possible solution?

 

[Pete_UK]

I suggest you step through the macro using F8 to check what is
happening at each point - it looks as if you are not pasting all the
values.

Hope this helps.

Pete