Looking for a vba solution for Column width formatting

[mikenelena]

I have the first 8 columns in my sheet (A-H) set to desired widths.

Does anyone know of any easy way to assign subsequent blocks of 8 (I-P), then Q-X), etc. to widths matching the first 8?

In other words, A, I, & Q... would all be 17.57. B, J, R would all be 9.43...

Many thanks in advance!

....Mike

 

[fhqwgads]

how many columns across are you going? if it's not too far you could just copy columns A-H and paste special column widths

 

[mikenelena]

Thanks fhqwgads, but I've got 30 sheets with about 28 blocks per sheet. I'm hoping for something faster...

 

[fhqwgads]

maybe something like

Sub Macro3()

Dim i As Long

    Columns("A:H").Copy
For i = 1 To 400 Step 8

    Columns(i).PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
Next i
End Sub

this should paste widths across 50 blocks of 8 columns hopefully

 

[mikenelena]

fhqwgads ,

That worked perfectly! Thanks so much! I added a line for last column, and a couple other embellishments.

Sub Macro3()


Dim i As Long
Dim lastcol As Long
Dim ws As Worksheet


Set ws = ThisWorkbook.ActiveSheet


Application.ScreenUpdating = False


lastcol = ws.Cells(2, ws.Columns.Count).End(xlToLeft).Column


    Columns("A:H").Copy
For i = 1 To lastcol Step 8


    Columns(i).PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
Next i


Application.CutCopyMode = False


Range("h14").Select


MsgBox "Finished"


End Sub