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