I posted this question in another newsgroup without a response.

Can someone tell me why this is getting a debug error.
It does what it is supposed to do but it stops with the debug error on the
following line:

sh.cut

Sub CopySheet1andRename()
With Range("B1") 'Cell with a date

' Format the name with leading zeros as it is needed in another sheet for a
lookup
' If the day or month does not have the leading 0 on single number days it
will not work.

myname = Format(Day(.Value), "00") & "-" & _
Format(Month(.Value), "00") & "-" & Right(.Value, 2)
End With
Sheets("Daily").Copy After:=Sheets(Sheets.Count)
' Copies the daily sheet and names the sheet with the date
' Example June 8, 2009 would be 08-06-09

With ActiveSheet
..Name = myname

'removes formulas
..UsedRange.Value = .UsedRange.Value

For Each sh In .Shapes
' This next line gets the debug error
' When I look at the copied sheet all formulas are gone
sh.Cut
Next sh
End With
End Sub

This Code works perfectly
There are only a couple of changes.
I use this code in another workbook

But I need it to work with the original due to naming the sheet with leading
zero's
for the day number for days less than the 10th.
There must be a way to work around this.


THE CODE THAT DOES WORK:
1. Sub CopySheet1andRename()
' The next line the date placement is changed
2. With Range("b6")
' The next line I do not format the day with leading zero's
' But the month is.
3. myname = Day(.Value) & "-" & _
Format(Month(.Value), "00") & "-" & Right(.Value, 2)
End With
' The name of the sheet is changed

Sheets("Daily Sales").Copy After:=Sheets(Sheets.Count)

With ActiveSheet
..Name = myname

'removes formulas
..UsedRange.Value = .UsedRange.Value

For Each sh In .Shapes
sh.Cut
Next sh
End With
End Sub

THE CODE THAT DOES NOT WORK:

Sub CopySheet1andRename()
1. With Range("B1")
2. myname = Format(Day(.Value), "00") & "-" & _
3. Format(Month(.Value), "00") & "-" & Right(.Value, 2)
End With
Sheets("Daily").Copy After:=Sheets(Sheets.Count)

With ActiveSheet
.Name = myname

'removes formulas
.UsedRange.Value = .UsedRange.Value

For Each sh In .Shapes
sh.Cut
Next sh
End With


"Ed Davis" wrote in message
...