Try


Dim WS As Worksheet
Set WS = ActiveSheet
WS.Copy
ActiveWorkbook.SaveAs Filename:=WS.Parent.Path & _
"\" & WS.Name & ".xls"


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"Simon Lloyd"
wrote
in message
...

Hi all, i'm trying to create a copy of a worksheet in a new
workbook if
Now is older than the date in Range("A1"), i want the new
workbook to
be the name of the original sheet (in future months the same
sheet will
be saved to this workbook, if i have sheets called "Week1",
"Week2" etc
i want them under the above criteria to be saved in workbooks
called
their respective names) the sheets will need to be saved as
thier
original name plus the contents of cell A1. i have been
struggling with
some code i found on this forum but to no avail and it halts at
the last
but one line "Object or with variable not set", the code below
doesnt
work how i described above....but i would like it to!

Hope you can help!
Regards,
Simon

Sub SheetSave()
Dim bk As Workbook, Sh As Worksheet
On Error Resume Next
Set bk = Workbooks("Saved Weeks.xls")
If Not bk Is Nothing Then
Set Sh = bk.Worksheets(ActiveSheet.Name)
If Not Sh Is Nothing Then
Application.DisplayAlerts = False
Sh.Delete
Application.DisplayAlerts = True
End If
Else
Set bk = Workbooks.Add()
bk.SaveAs "C:\Myfolder\Destination.xls"
End If
On Error GoTo 0
ActiveSheet.Name = "Week " & "Starting " & Range("A1").Text
ThisWorkbook.Worksheets(ActiveSheet.Name).Copy ',
After:=bk.Sheets(bk.Sheets.Count)
bk.Save
End Sub


--
Simon Lloyd
------------------------------------------------------------------------
Simon Lloyd's Profile:
http://www.excelforum.com/member.php...fo&userid=6708
View this thread:
http://www.excelforum.com/showthread...hreadid=552803