Hi

It would be eaisier to help if you told which error message you get...

Regards,
Per

On 15 Jun., 12:43, "Ed Davis" wrote:
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

...



- Skjul tekst i anførselstegn -

- Vis tekst i anførselstegn -