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

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

Thanks for the reply Chip, looks like a really simple solution, to save
the worksheet as the worksheetname and cell A1.value would i have to
add ActiveSheet.Name = "Week " & "Starting " & Range("A1").Text
as i would like to save any worksheet in the workbook named by the
worksheet as its own name +A1.value.....if you like....i would want to
save now and in future all sheets named Week1 in a workbook called
week1 but when the sheet is actually saved in this work book name the
sheet its own name+A1.value, i have 6 weeks in all and when any week
becomes older than N0W by 1 week then execute the save sheet code,
eventually i will end up with 6 workbooks named Week1, Week2....etc all
growing in size with sheets being added as they meet the criteria for
saving!

i've just read all that and it sounded murky to say the least....still
that was my brain dump!......ok brain CELL dump!

Regards,
Simon


--
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