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

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.


"Ed Davis" wrote in message
...
|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
| ...
|
|
|

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 -

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 -

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 -

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 -

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 -

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 -