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

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

Jim Thomlinson


"Code Numpty" wrote:

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

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")

"Code Numpty" wrote:

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

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" wrote:

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")

"Code Numpty" wrote:

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

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.

"Barb Reinhardt" wrote:

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" wrote:

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")

"Code Numpty" wrote:

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

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

Jim Thomlinson


"Joel" wrote:

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.

"Barb Reinhardt" wrote:

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" wrote:

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")

"Code Numpty" wrote:

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

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" wrote:

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

Jim Thomlinson


"Joel" wrote:

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.

"Barb Reinhardt" wrote:

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" wrote:

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")

"Code Numpty" wrote:

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