Debug Error
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 -
|