Hi Steve

Use this then

..SaveAs "C:/Audit Reports/" & Format(Date, "dd-mm-yy") & ".xls"



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


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

Excellent thankyou

one question please
.SaveAs "C:/Audit Reports/ Audit Summary.xls"
can I change it to
.SaveAs "C:/Audit Reports/ dd-mm-yy.xls"

The reason is that when saved it is given a name automatically in this case
a date.


"Ron de Bruin" wrote:

Hi Steve

You must remove the End with after the SaveAs line
(there is one after the close line)

Only add this part in the original macro (example 2)
http://www.rondebruin.nl/mail/folder1/mail2.htm

With ActiveSheet.UsedRange
.Value = .Value
End With


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


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

Ron It is highliting .SendMail of the below and is giving me a error
Invalid or unqualified reference
Please what have i got wrong thankyou.

.SendMail Addr(N), _


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 ActiveSheet.UsedRange
.Value = .Value
End With

With wb
.SaveAs "C:/Audit Reports/ Audit Summary.xls"
End With
.SendMail Addr(N), _
"Audit Summary Report"
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
Next N
Application.ScreenUpdating = True
End Sub





"Ron de Bruin" wrote:

Hi Steved

You try to save

ActiveSheet.UsedRange
instead of ActiveWorkbook(or wb)

Use it like this

Set wb = ActiveWorkbook
With ActiveSheet.UsedRange
.Value = .Value
End With

With wb
.SaveAs "Sheet ....................
End With




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


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

Before I made the changes below it worked fine.

The error message is "Object dosen't support this property or method"

I made 2 changes to below

1 is to ask the worksheet to change to values.
2 to save file.

Set wb = ActiveWorkbook
With ActiveSheet.UsedRange
.Value = .Value
.SaveAs "Sheet " & Shname(N) _
& " " & strdate & "C:/Audit Reports/ Audit Summary.xls"




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 all Depots", "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 ActiveSheet.UsedRange
.Value = .Value
.SaveAs "Sheet " & Shname(N) _
& " " & strdate & "C:/Audit Reports/ Audit Summary.xls"
.SendMail Addr(N), _
"Audit Summary Report"
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
Next N
Application.ScreenUpdating = True
End Sub

Thankyou.