find columns width

[RompStar]

I have a need to duplicate the column width from a workbook to a new workbook.

My column Range is A ~ AG. Is there some VBA code that I can employ that would scan this column range, find out their column widths ?

And then I could maybe play it back on a different sheet that is already setup to duplicate the columns exactly, just need to mimic the width, but the data is all different.

Thanks!

 

[VoG]

You could do something like this

ThisWorkbook.Sheets(1).Columns("A:G").Copy
ActiveWorkbook.Sheets(1).Range("A1").PasteSpecial Paste:=xlPasteColumnWidths

 

[RompStar]

this is cool, but is there a way to capture the actual column width values ?

A - 12.25
B - 13.01
and so on...

 

[VoG]

Try

Sub ColWidths()
Dim msg As String, i As Long
For i = 1 To 33
    msg = msg & i & vbTab & Columns(i).ColumnWidth & vbNewLine
Next i
MsgBox msg, vbInformation, "Column widths"
End Sub

 

[RompStar]

coool, I made small modification, because I wanted to capture the values to a sheet

I just run it on the sheet that had the widths that I needed and wrote their values to a different sheet, thanks!

Sub ColWidths()

Dim i As Long

For i = 1 To 33
Sheets("values").Range("A" & i).Value = i
Sheets("values").Range("B" & i).Value = Columns(i).ColumnWidth
Next i

MsgBox "Done"

End Sub