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").RefersToRan ge.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.

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

On Oct 11, 9:54 am, Sunnyskies
wrote:
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").RefersToRan ge.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.

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" wrote:

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

On Oct 11, 9:54 am, Sunnyskies
wrote:
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").RefersToRan ge.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.

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

On Oct 11, 1:31 pm, Sunnyskies
wrote:
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" wrote:
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

On Oct 11, 9:54 am, Sunnyskies
wrote:
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").RefersToRan ge.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.- Hide quoted text -

- Show quoted text -