copying range column widths

[Guest]

Greetings

Is there a quick way to apply a range's column widths to another range

For example, a user may populate a query table, delete some columns, adjust some columns to their liking, and then copy that modified range to another worksheet

I can copy and paste a ranges's data into another worksheet, but I can't (easily) preserve the column widths of the original range. Is there some slick code to copy the original ranges's column widths to the copied ranges columns without looping

Thanks a lot

 

[Ron de Bruin]

If you use Excel 2000 or higher you can use PasteSpecial

Sheets("Sheet1").Range("A11").Copy
Sheets("Sheet2").Range("a1").PasteSpecial Paste:=8
' Paste:=8 will copy the column width in Excel 2000 and higher
Application.CutCopyMode = False


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)

Greetings,

Is there a quick way to apply a range's column widths to another range?

For example, a user may populate a query table, delete some columns, adjust some columns to their liking, and then copy that

modified range to another worksheet.

I can copy and paste a ranges's data into another worksheet, but I can't (easily) preserve the column widths of the original

range. Is there some slick code to copy the original ranges's column widths to the copied ranges columns without looping?

 

[Beto]

Greetings,

Is there a quick way to apply a range's column widths to another range?

For example, a user may populate a query table, delete some columns, adjust some columns to their liking, and then copy that modified range to another worksheet.

I can copy and paste a ranges's data into another worksheet, but I can't (easily) preserve the column widths of the original range. Is there some slick code to copy the original ranges's column widths to the copied ranges columns without looping?

Here is another way that doesn't involve selection of the range.

Sub CopyColumnWidths()
For i = 1 To Range("A:J").Columns.Count
Sheets("Sheet2").Range("A:J").Columns(i).ColumnWidth = _
Sheets("Sheet1").Range("A:J").Columns(i).ColumnWidth
Next i
End Sub

Regards,