copying range column widths

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
If you use Excel 2000 or higher you can use PasteSpecial

Sheets("Sheet1").Range("A1:D1").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)




rexsalius said:
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
Click to expand...
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
Click to expand...
range. Is there some slick code to copy the original ranges's column widths to the copied ranges columns without looping?
 
rexsalius said:
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?
Click to expand...

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,
 
Back
Top