XL2000: "PasteSpecial method of Range class failed" Error When Running a Macro (231090)

SYMPTOMS

When you run a macro that contains the xlColumnWidths member, you receive the following error message:

Run-time error '1004':
PasteSpecial method of Range class failed"

CAUSE

This error message occurs when the following conditions are true:

You have a line of code in your macro similar to the following:
```
Selection.PasteSpecial Paste:=xlColumnWidths
						
```
-or-
You record a macro and paste the column widths using the Paste Special dialog box (on the Edit menu, click Paste Special).

WORKAROUND

To work around this problem use the number 8 in place of the xlColumnWidths value. The number 8 represents the Column Widths paste constant which is part of Microsoft Excel. The following example illustrates this:

Change this code

Selection.PasteSpecial Paste:=xlColumnWidths

to this:

Selection.PasteSpecial Paste:=8

MORE INFORMATION

Although Microsoft Excel displays the xlColumnWidths member when you use the Macro Recorder to record a macro, it is not defined in the object model of Excel. Because it is not defined in the object model, it does not appear in the Object Browser in the Visual Basic Editor (on the View menu, click Object Browser).

If your code contains multiple instances of the xlColumnWidths member, make the following declaration at the beginning of the macro, which will prevent you from having to change every occurrence of xlColumnWidths:

Const xlColumnWidths = 8