renaming worksheets by date value

[Atishoo]

Hi
Am using an open event to calculate if a date on a page has passed if so it
coppies the page to another workbook for archiving.

I want to change the name of the worksheet copied to the date that has
passed (a date value contained within a cell. The date value is formatted to
2-feb-2009 but it still returns error message on this!

any ideas??





Private Sub Workbook_Open()
If Worksheets("sheet3").Range("k4").Value >
Worksheets("sheet3").Range("k9").Value Then


Workbooks.Open Filename:="AOT Weekend Duties Record.XLS"
Workbooks("weekend duties2").Worksheets("Sheet2").Copy
after:=Workbooks("AOT Weekend Duties Record.xls").Worksheets("Sheet1")
Workbooks("AOT Weekend Duties Record.xls").Worksheets("sheet2").Name
= Workbooks("weekend duties2").Worksheets("sheet3").Range("k9").Value
Workbooks("AOT Weekend Duties Record.xls").Close
Worksheets("sheet3").Range("k9").Value =
Worksheets("sheet3").Range("k8").Value

End If
End Sub

 

[Dave Peterson]

If you know that the format is always correct:

with activesheet 'or what you want
.name = .range("a1").text
end with

If you're cautious (and I would be):

with activesheet 'or what you want
.name = format(.range("a1").value, "d-mmm-yyyy")
end with

 

[Atishoo]

took the cautious route and it works great! thanks

ps you dont happen to know if there is a suffix to the copy command to stop
it copying objects (command buttons) with it?? ie copy worksheets after.....
copy objects = false

 

[Dave Peterson]

You could create a new worksheet, then copy|paste special|formulas, then
copy|paste special|formats (etc).

Or you could copy the sheet and then delete the objects.

If you know what to delete by name, it isn't too hard.

If you want to delete (almost) all the objects, you have to be a little more
careful that you don't break things.

There are lots of shapes that you may want to keep (comments, autofilter arrows,
datavalidation arrows).

Ron de Bruin has lots of tips here:
http://www.rondebruin.nl/controlsobjectsworksheet.htm