Error in code.

[Ed Davis]

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")
myname = Format(Day(.Value), "00") & "-" & _
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]

I think I found the problem but not sure.
The following code works great.
The only change is on line 3.
But I need it to work with the original due to naming the sheet with 1
number for days less than 10.
There must be a way to work around this.


THE CODE THAT DOES WORK:
1. Sub CopySheet1andRename()
2. With Range("b6")
3. myname = Day(.Value) & "-" & _
Format(Month(.Value), "00") & "-" & Right(.Value, 2)
End With

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

 

[Norie]

Ed

Are you trying to format a date for the name and have 2 digits for the day
eg 01,02 ... 10, 11,12 etc

This works for me if B6 has a date in it.

myname = Format(Range("B6").Value, "dd-mm-yy")