Debug Error

E

Ed Davis

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
 
H

Homey

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.


|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
|
|
| | >
| >
|
 
P

Per Jessen

Hi

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

Regards,
Per
 
E

Ed Davis

The error description is as follows:
Application - Defined or object defined error


Hi

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

Regards,
Per
 
P

Per Jessen

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.
 
E

Ed Davis

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



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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Error in code. 2
Automatically name all Sheet Tabs from a list 4
Copy Sheet to new Sheet and clear cells on original sheets 7
F.A.O Mike H 3
Excel 2003 help 1
Make Worksheets Given a Certain YEAR 5
Fix charts as embedded pictures 3
Link 3

Top