Hello Ron from Steved

Ok is it possible with the below please to hightlight the
whole page and change all the formulas to a value, so I am
asking please is their a formula you can add to the below
to achieve this.
Thankyou.

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 h-mm-ss")
Shname = Array("1-City", "2-Roskill", "3-Papakura", "4-
Wiri", "5-Shore", "6-Orewa", "7-Swanson", "8-Panmure")
Addr = Array("1-Depot", "2-Depot", "4-Depot", "4-
Depot", "5-Depot", "5-Depot", "7-Depot", "1-Depot")

Application.ScreenUpdating = False

For N = LBound(Shname) To UBound(Shname)
Sheets(Shname(N)).Copy
Set wb = ActiveWorkbook
With wb
.SaveAs "Sheet " & Shname(N) _
& " " & strdate & ".xls"
.SendMail Addr(N), _
"Depot Annulments"
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
Next N
Application.ScreenUpdating = True
End Sub

I'm not Ron, but you could try this:

make this portion:

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

look like:

For N = LBound(Shname) To UBound(Shname)
Sheets(Shname(N)).Copy
with activesheet.usedrange
.value = .value
end with
...

Steved wrote:

Hello Ron from Steved

Ok is it possible with the below please to hightlight the
whole page and change all the formulas to a value, so I am
asking please is their a formula you can add to the below
to achieve this.
Thankyou.

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 h-mm-ss")
Shname = Array("1-City", "2-Roskill", "3-Papakura", "4-
Wiri", "5-Shore", "6-Orewa", "7-Swanson", "8-Panmure")
Addr = Array("1-Depot", "2-Depot", "4-Depot", "4-
Depot", "5-Depot", "5-Depot", "7-Depot", "1-Depot")

Application.ScreenUpdating = False

For N = LBound(Shname) To UBound(Shname)
Sheets(Shname(N)).Copy
Set wb = ActiveWorkbook
With wb
.SaveAs "Sheet " & Shname(N) _
& " " & strdate & ".xls"
.SendMail Addr(N), _
"Depot Annulments"
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
Next N
Application.ScreenUpdating = True
End Sub

--

Dave Peterson

Hi Steved

http://www.rondebruin.nl/mail/folder1/mail2.htm
Dave already posted a good solution I see

If you click on the tip link on the page above you can see a other solution also.
http://www.rondebruin.nl/mail/tips1.htm
Dave's solution will not make values of external links (to other workbooks)
Maybe this is no problem for you



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



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

Ok is it possible with the below please to hightlight the
whole page and change all the formulas to a value, so I am
asking please is their a formula you can add to the below
to achieve this.
Thankyou.

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 h-mm-ss")
Shname = Array("1-City", "2-Roskill", "3-Papakura", "4-
Wiri", "5-Shore", "6-Orewa", "7-Swanson", "8-Panmure")
Addr = Array("1-Depot", "2-Depot", "4-Depot", "4-
Depot", "5-Depot", "5-Depot", "7-Depot", "1-Depot")

Application.ScreenUpdating = False

For N = LBound(Shname) To UBound(Shname)
Sheets(Shname(N)).Copy
Set wb = ActiveWorkbook
With wb
.SaveAs "Sheet " & Shname(N) _
& " " & strdate & ".xls"
.SendMail Addr(N), _
"Depot Annulments"
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
Next N
Application.ScreenUpdating = True
End Sub

Thankyou Very Much.

-----Original Message-----
I'm not Ron, but you could try this:

make this portion:

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

look like:

For N = LBound(Shname) To UBound(Shname)
Sheets(Shname(N)).Copy
with activesheet.usedrange
.value = .value
end with
...

Steved wrote:

Hello Ron from Steved

Ok is it possible with the below please to hightlight
the
whole page and change all the formulas to a value, so I
am
asking please is their a formula you can add to the
below
to achieve this.
Thankyou.

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 h-mm-ss")
Shname = Array("1-City", "2-Roskill", "3-
Papakura", "4-
Wiri", "5-Shore", "6-Orewa", "7-Swanson", "8-Panmure")
Addr = Array("1-Depot", "2-Depot", "4-Depot", "4-
Depot", "5-Depot", "5-Depot", "7-Depot", "1-Depot")

Application.ScreenUpdating = False

For N = LBound(Shname) To UBound(Shname)
Sheets(Shname(N)).Copy
Set wb = ActiveWorkbook
With wb
.SaveAs "Sheet " & Shname(N) _
& " " & strdate & ".xls"
.SendMail Addr(N), _
"Depot Annulments"
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
Next N
Application.ScreenUpdating = True
End Sub

--

Dave Peterson
.

I don't understand this portion:

Dave's solution will not make values of external links (to other workbooks)
Maybe this is no problem for you

I bet you're not writing about a simple formula link???

Ron de Bruin wrote:

Hi Steved

http://www.rondebruin.nl/mail/folder1/mail2.htm
Dave already posted a good solution I see

If you click on the tip link on the page above you can see a other solution also.
http://www.rondebruin.nl/mail/tips1.htm
Dave's solution will not make values of external links (to other workbooks)
Maybe this is no problem for you

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

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

Ok is it possible with the below please to hightlight the
whole page and change all the formulas to a value, so I am
asking please is their a formula you can add to the below
to achieve this.
Thankyou.

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 h-mm-ss")
Shname = Array("1-City", "2-Roskill", "3-Papakura", "4-
Wiri", "5-Shore", "6-Orewa", "7-Swanson", "8-Panmure")
Addr = Array("1-Depot", "2-Depot", "4-Depot", "4-
Depot", "5-Depot", "5-Depot", "7-Depot", "1-Depot")

Application.ScreenUpdating = False

For N = LBound(Shname) To UBound(Shname)
Sheets(Shname(N)).Copy
Set wb = ActiveWorkbook
With wb
.SaveAs "Sheet " & Shname(N) _
& " " & strdate & ".xls"
.SendMail Addr(N), _
"Depot Annulments"
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
Next N
Application.ScreenUpdating = True
End Sub


--

Dave Peterson

Hi Dave

You are correct
A few weeks back a OP have problems with the value code
When I send him a example with PasteSpecial it was working.

In his thank you note he say this
I forgot to take the comment tags out.. Opps. both work great.

Sorry I am getting old <g


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



"Dave Peterson" wrote in message ...
I don't understand this portion:

Dave's solution will not make values of external links (to other workbooks)
Maybe this is no problem for you

I bet you're not writing about a simple formula link???

Ron de Bruin wrote:

Hi Steved

http://www.rondebruin.nl/mail/folder1/mail2.htm
Dave already posted a good solution I see

If you click on the tip link on the page above you can see a other solution also.
http://www.rondebruin.nl/mail/tips1.htm
Dave's solution will not make values of external links (to other workbooks)
Maybe this is no problem for you

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

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

Ok is it possible with the below please to hightlight the
whole page and change all the formulas to a value, so I am
asking please is their a formula you can add to the below
to achieve this.
Thankyou.

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 h-mm-ss")
Shname = Array("1-City", "2-Roskill", "3-Papakura", "4-
Wiri", "5-Shore", "6-Orewa", "7-Swanson", "8-Panmure")
Addr = Array("1-Depot", "2-Depot", "4-Depot", "4-
Depot", "5-Depot", "5-Depot", "7-Depot", "1-Depot")

Application.ScreenUpdating = False

For N = LBound(Shname) To UBound(Shname)
Sheets(Shname(N)).Copy
Set wb = ActiveWorkbook
With wb
.SaveAs "Sheet " & Shname(N) _
& " " & strdate & ".xls"
.SendMail Addr(N), _
"Depot Annulments"
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
Next N
Application.ScreenUpdating = True
End Sub


--

Dave Peterson

The only difference I've seen between .value = .value and copy|paste
special|values is when you have cells that have character by character
formatting.

the .value = .value changes the cell's format to match the first character in
the cell (IIRC).

the paste special|values keeps that formatting in tact.

===

http://www.aarp.org
?????

<gd&r



Ron de Bruin wrote:

Hi Dave

You are correct
A few weeks back a OP have problems with the value code
When I send him a example with PasteSpecial it was working.

In his thank you note he say this
I forgot to take the comment tags out.. Opps. both work great.

Sorry I am getting old <g

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

"Dave Peterson" wrote in message ...
I don't understand this portion:

Dave's solution will not make values of external links (to other workbooks)
Maybe this is no problem for you

I bet you're not writing about a simple formula link???

Ron de Bruin wrote:

Hi Steved

http://www.rondebruin.nl/mail/folder1/mail2.htm
Dave already posted a good solution I see

If you click on the tip link on the page above you can see a other solution also.
http://www.rondebruin.nl/mail/tips1.htm
Dave's solution will not make values of external links (to other workbooks)
Maybe this is no problem for you

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

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

Ok is it possible with the below please to hightlight the
whole page and change all the formulas to a value, so I am
asking please is their a formula you can add to the below
to achieve this.
Thankyou.

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 h-mm-ss")
Shname = Array("1-City", "2-Roskill", "3-Papakura", "4-
Wiri", "5-Shore", "6-Orewa", "7-Swanson", "8-Panmure")
Addr = Array("1-Depot", "2-Depot", "4-Depot", "4-
Depot", "5-Depot", "5-Depot", "7-Depot", "1-Depot")

Application.ScreenUpdating = False

For N = LBound(Shname) To UBound(Shname)
Sheets(Shname(N)).Copy
Set wb = ActiveWorkbook
With wb
.SaveAs "Sheet " & Shname(N) _
& " " & strdate & ".xls"
.SendMail Addr(N), _
"Depot Annulments"
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
Next N
Application.ScreenUpdating = True
End Sub


--

Dave Peterson

--

Dave Peterson