How do you paste column widths in a macro? 2

[syl93]

I must be missing something ...
I am writing a macro in Excel. I have a selected range that I copied, and I want to paste the column widths of the selected range to a new range. This can be done by going to the Edit - Paste Special menu and selecting column widths. I recorded that action as a test macro, and it gave me the following statement:

Selection.PasteSpecial Paste:=xlColumnWidths, _
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False


The problem is, that statement will not run. I get a Run-time error 1004 - PasteSpecial method of Range class failed.

Looking at the help files on the PasteSpecial, xlColumnWidths does not appear to be a paste option, so I am asking if there is another way to copy the column widths without just looping through each column to manually set the column width property?

 

[xlhelp]

Hi!
You need to put the following declaration at the beginning of your macro:

Const xlColumnWidths = 8

Then in place of

Selection.PasteSpecial Paste:=xlColumnWidths, _
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

put the following code

Selection.PasteSpecial Paste:=8

The number 8 is ColumnWidth Paste constant that Excel uses.

Hope that helps.

Indu

 

[syl93]

That worked perfectly. Thanks!