Run-time error ‘1004’

[Code Numpty]

I have the following macro in a workbook and a user keeps getting the same
runtime error that I cannot duplicate.

MACRO CODE---------------------------------
Sub Quote_Wrapup()
'To stop screen flicker
Application.ScreenUpdating = False

Range("quote_date").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Range("qdata5,qdata6").Font.ColorIndex = 2

'To delete delivery address lines if 1st line empty
If IsEmpty(Range("deliver_line1")) _
Then Sheets(1).Range("deliver_rows").EntireRow.Delete
'No End If required as only one action as a result of the If

Range("Item_Nos").SpecialCells(xlCellTypeBlanks).EntireRow.Delete

Call paste_values

Sub paste_values()
With Sheet1.Range("A:F")
.Copy
.PasteSpecial xlPasteValues
End With
Application.CutCopyMode = False
End Sub
------------------------------------------------------
The user gets

Run-time error ‘1004’
The information cannot be pasted because the copy area and the paste area
are not the same size and shape.

and the when debugged the following line is highlighted.
.........................................................................
.PasteSpecial xlPasteValues
.........................................................................

As the copy range is ("A:F") .i.e. columns I don't understand how this can
happen.

Grateful for any advice on this puzzle.

 

[Jim Thomlinson]

I do not see anything that should generate that error. As a guess there is
more to this such as conversion to XL2007 or such. That being said to convert
to values can be done in a single line something like this...

Sheet1.Range("A:F").Value = Sheet1.Range("A:F").Value

No copy and paste or such...

 

[Joel]

The may be a problem with sheet1 rreference in this line

With Sheet1.Range("A:F")

Sheet1 is always defined. shouuld use

With Sheets(1).Range("A:F")

or better

With Sheets("sheet1").Range("A:F")

 

[Barb Reinhardt]

Why should they use

With Sheets(1).Range("A:F")

if Sheet1 is the codename for the sheet?

That doesn't make sense to me.

Barb Reinhardt

 

[Joel]

Barb: If you delete Sheet one you no longer have a sheet1 codename. And if
you rename sheet2 sheet1 then sheet1 has a code name sheet2.

 

[Jim Thomlinson]

I too am not following you... If Sheet1 does not exist then you get a 424
Object Required error (or a compile error if you are using option explicit).

Sheets(1) is about the worst code you can use as there are any number of
things that can change the index numbers of the sheets, so there is no
reliable way of knowing which sheet is at index number 1.

Sheets("Sheet1") is also not great code as it is vulnerable to end user
changes to the tab name...

The most reliable code uses the code name of the sheet to get a direct
handle to the object...

What do you mean by "Sheet1 is always defined". If I delete the sheet with
code name Sheet1, then it is not defined. The object is gone.

It is definitely advisable to change the code names of the sheets. If I
delete Sheet1 it is gone. But if I close the book and reopen it and add a new
sheet it will be a new sheet with code name Sheet1... By changing the code
name to something like shtMySheet I get around that potential problem and end
up with foolproof code.

 

[Joel]

It is only fool proof if somebody doesn't delete the sheet. I like to have
the sheet names visible so I can easily see the problem and fix the problem
when sheets are re-named, moved, or deleted.

 

[Jim Thomlinson]

To each his own... But your explanation still does not expain the error. If
the sheet does not exist then you get a 424 or compile error.

 

[Barb Reinhardt]

Jim,

FWIW, I use worksheet code name all the time now. That way, if I (or a
user) decides to change the worksheet name, the code still functions. And if
the worksheet isn't there, I build in an error check for that as much as
possible.

Barb Reinhardt

 

[Jim Thomlinson]

You and me both... BTW I have seen the quality of the code you have been
posting and I must say... Very Nice. A long way from your original efforts.
At this point you can hold your own with the best of 'em...

 

[Barb Reinhardt]

Thanks. I learned it all here (+ doing a lot of googling). I remember the
first piece of code I did. It took weeks. Now I could probably write it in
a couple of hours and be close.

Barb Reinhardt

 

[Code Numpty]

It is a little difficult as I cannot seem to replicate the problem and the
user is on Excel 2003 too. I can't remember why I went with my original
method in the first place but having read all the replies and interesting
discussion I have tried Jim's.

Sheet1.Range("A:F").Value = Sheet1.Range("A:F").Value

I await what happens when tested by the user who gets the error.