not all shapes can be cut. check which shape is making error. maybe it a
cell comment.

peole may not answer your post because it confusing and have to much code
sometimes same code 2 or 3 times. try to narrow down to problem code
wothout posting lots of extra code.


"Ed Davis" wrote in message
...
|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
| ...
|
|
|