Hello from Steved

Please I would to know where to put the below code into the Sub Mail_test()
code which is also below.

The reason is the worksheet which I call summary has 8 other worksheets
which read formulas from the summary sheet. I want to please have the other
sheets
paste special value. as I tried but not finding any success as the other
sheets that are emailed still have the formulas hence when opened at the
destination they see REF!. Thanks for taking time on my important issue.


Worksheets.Select
Cells.Copy
Cells.PasteSpecial xlPasteValues
Cells(1).Select
Worksheets(1).Select
Application.CutCopyMode = False


Sub Mail_test()
Dim wb As Workbook
Dim strdate As String
Dim Shname As Variant
Dim Addr As Variant
Dim N As Integer

strdate = Format(Now, "dd-mm-yy")
Shname = Array("Summary", "City Depot (1)", "Roskill Depot (2)",
"Papakura Depot (3)", "Wiri Depot (4)", "Shore Depot (5)", "Orewa Depot (6)",
"Swanson Depot (7)", "Panmure Depot (8)")
Addr = ", ",
", ", ",
", ", ",
")

Application.ScreenUpdating = False

For N = LBound(Shname) To UBound(Shname)
Sheets(Shname(N)).Copy
Set wb = ActiveWorkbook

With wb
.SaveAs "C:/Audit Reports/" & Format(Now, "dd-mm-yy hh-mm-ss") &
".xls"
.SendMail Addr(N), _
"Audit Summary Report"
.Close False
End With
Next N
Application.ScreenUpdating = True
End Sub

Hi Steve

You must copy this
Cells.Copy
Cells.PasteSpecial xlPasteValues
Cells(1).Select
Application.CutCopyMode = False
after
Sheets(Shname(N)).Copy

If this is not working for you send me your test workbook private and i look at
it on sunday evening.


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Steved" wrote in message ...
Hello from Steved

Please I would to know where to put the below code into the Sub Mail_test()
code which is also below.

The reason is the worksheet which I call summary has 8 other worksheets
which read formulas from the summary sheet. I want to please have the other
sheets
paste special value. as I tried but not finding any success as the other
sheets that are emailed still have the formulas hence when opened at the
destination they see REF!. Thanks for taking time on my important issue.


Worksheets.Select
Cells.Copy
Cells.PasteSpecial xlPasteValues
Cells(1).Select
Worksheets(1).Select
Application.CutCopyMode = False


Sub Mail_test()
Dim wb As Workbook
Dim strdate As String
Dim Shname As Variant
Dim Addr As Variant
Dim N As Integer

strdate = Format(Now, "dd-mm-yy")
Shname = Array("Summary", "City Depot (1)", "Roskill Depot (2)",
"Papakura Depot (3)", "Wiri Depot (4)", "Shore Depot (5)", "Orewa Depot (6)",
"Swanson Depot (7)", "Panmure Depot (8)")
Addr = ", ",
", ", ",
", ", ",
")

Application.ScreenUpdating = False

For N = LBound(Shname) To UBound(Shname)
Sheets(Shname(N)).Copy
Set wb = ActiveWorkbook

With wb
.SaveAs "C:/Audit Reports/" & Format(Now, "dd-mm-yy hh-mm-ss") &
".xls"
.SendMail Addr(N), _
"Audit Summary Report"
.Close False
End With
Next N
Application.ScreenUpdating = True
End Sub

Thanks Ron sorry about the delay yesterday I could read but the reply part
was not functioning.

"Ron de Bruin" wrote:

Hi Steve

You must copy this
Cells.Copy
Cells.PasteSpecial xlPasteValues
Cells(1).Select
Application.CutCopyMode = False
after
Sheets(Shname(N)).Copy

If this is not working for you send me your test workbook private and i look at
it on sunday evening.


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Steved" wrote in message ...
Hello from Steved

Please I would to know where to put the below code into the Sub Mail_test()
code which is also below.

The reason is the worksheet which I call summary has 8 other worksheets
which read formulas from the summary sheet. I want to please have the other
sheets
paste special value. as I tried but not finding any success as the other
sheets that are emailed still have the formulas hence when opened at the
destination they see REF!. Thanks for taking time on my important issue.


Worksheets.Select
Cells.Copy
Cells.PasteSpecial xlPasteValues
Cells(1).Select
Worksheets(1).Select
Application.CutCopyMode = False


Sub Mail_test()
Dim wb As Workbook
Dim strdate As String
Dim Shname As Variant
Dim Addr As Variant
Dim N As Integer

strdate = Format(Now, "dd-mm-yy")
Shname = Array("Summary", "City Depot (1)", "Roskill Depot (2)",
"Papakura Depot (3)", "Wiri Depot (4)", "Shore Depot (5)", "Orewa Depot (6)",
"Swanson Depot (7)", "Panmure Depot (8)")
Addr = ", ",
", ", ",
", ", ",
")

Application.ScreenUpdating = False

For N = LBound(Shname) To UBound(Shname)
Sheets(Shname(N)).Copy
Set wb = ActiveWorkbook

With wb
.SaveAs "C:/Audit Reports/" & Format(Now, "dd-mm-yy hh-mm-ss") &
".xls"
.SendMail Addr(N), _
"Audit Summary Report"
.Close False
End With
Next N
Application.ScreenUpdating = True
End Sub

Hi Steve

The problem is that you use Indirect in your formulas to another worksheet in the workbook

I have send you a workaround

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Steved" wrote in message ...
Thanks Ron sorry about the delay yesterday I could read but the reply part
was not functioning.

"Ron de Bruin" wrote:

Hi Steve

You must copy this
Cells.Copy
Cells.PasteSpecial xlPasteValues
Cells(1).Select
Application.CutCopyMode = False
after
Sheets(Shname(N)).Copy

If this is not working for you send me your test workbook private and i look at
it on sunday evening.


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Steved" wrote in message ...
Hello from Steved

Please I would to know where to put the below code into the Sub Mail_test()
code which is also below.

The reason is the worksheet which I call summary has 8 other worksheets
which read formulas from the summary sheet. I want to please have the other
sheets
paste special value. as I tried but not finding any success as the other
sheets that are emailed still have the formulas hence when opened at the
destination they see REF!. Thanks for taking time on my important issue.


Worksheets.Select
Cells.Copy
Cells.PasteSpecial xlPasteValues
Cells(1).Select
Worksheets(1).Select
Application.CutCopyMode = False


Sub Mail_test()
Dim wb As Workbook
Dim strdate As String
Dim Shname As Variant
Dim Addr As Variant
Dim N As Integer

strdate = Format(Now, "dd-mm-yy")
Shname = Array("Summary", "City Depot (1)", "Roskill Depot (2)",
"Papakura Depot (3)", "Wiri Depot (4)", "Shore Depot (5)", "Orewa Depot (6)",
"Swanson Depot (7)", "Panmure Depot (8)")
Addr = ", ",
", ", ",
", ", ",
")

Application.ScreenUpdating = False

For N = LBound(Shname) To UBound(Shname)
Sheets(Shname(N)).Copy
Set wb = ActiveWorkbook

With wb
.SaveAs "C:/Audit Reports/" & Format(Now, "dd-mm-yy hh-mm-ss") &
".xls"
.SendMail Addr(N), _
"Audit Summary Report"
.Close False
End With
Next N
Application.ScreenUpdating = True
End Sub