Thanks for your reply, I'm glad to help.

Regards,
Per

"Ed Davis" skrev i meddelelsen
...
OK I found that there was a comment in one of the cells.
Thank you for your help.

"Ed Davis" wrote in message
...
The answers to these things mean nothing to me.
Sorry if I seem so dumb but I have been working with excel for a few
years but never tried to remove formulas like this before.

Debug.print sh.type
4
Debug.print sh.name
comment 4



"Per Jessen" wrote in message
...
I think as Homey stated in his post, the problem is related to the
type of shape.

When you get the error, click Debug, and in the Immediate window enter
(CTRL+G to show Immediate window if it isn't visible) :

Debug.Print sh.Type
Debug.Print sh.Name

Press enter after each line. This should tell you exactly which shape
is causing the problem, or maybe this sheet does not have any shapes.

Hopes this helps.

---
Per


On 16 Jun., 03:24, "Ed Davis" wrote:
The error description is as follows:
Application - Defined or object defined error

"Per Jessen" wrote in message

...
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 -- Skjul tekst i anførselstegn -

- Vis tekst i anførselstegn -