CODEWITHSUNDEEP
excelvba
Maccus
Maccus

Reputation: 31

Setting a column width on multiple columns in Excel VBA

Jeeped and CLR kindly provided the code that added a variable number of columns to a worksheet named sht02AnalysisSummary starting at Column D whilst copying the borders and formulae of Column C. 

AddCol = txtNrEvaluated

With sht02AnalysisSummary
    Set rangeCopy = .Range(.Cells(3, "C"), .Cells(.Rows.Count, "C").End(xlUp))
    rangeCopy.Copy Destination:=.Cells(3, .Columns.Count).End(xlToLeft).Offset(0, 1).Resize(rangeCopy.Rows.Count, AddCol)
End With

This code, however, does not copy the format of Column C in terms of width and although I have experimented with EntireColumn.ColumnWidth = 15 with the With End With and even its own With End With, I have been unsuccessful.

Any assistance would be much appreciated.

Upvotes: 2

Views: 11146

Answers (2)

Siddharth Rout
Siddharth Rout

Reputation: 149335

Expanding on my comment below your question.

Column Widths are only copied across when you are copying across entire columns and nor ranges. Also rng.ColumnWidth = 15 should work

Is this what you are trying?

Dim rangeCopy As Range, destRng As Range

AddCol = 2

With sht02AnalysisSummary
    Set rangeCopy = .Range(.Cells(3, "C"), .Cells(.Rows.Count, "C").End(xlUp))
    Set destRng = .Cells(3, .Columns.Count).End(xlToLeft).Offset(0, 1).Resize(rangeCopy.Rows.Count, AddCol)
    rangeCopy.Copy destRng
    destRng.ColumnWidth = 15
End With

Screenshot

enter image description here

Upvotes: 3

CLR
CLR

Reputation: 12289

If you want to copy the source tab's column C width across to the new columns, you could use:

Dim rangeCopy As Range, rangePaste As Range
Dim Addcol As Integer

Addcol = NrEvaluated

With sht02AnalysisSummary

    Set rangeCopy = .Range(.Cells(3, "C"), .Cells(.Rows.Count, "C").End(xlUp))
    Set rangePaste = .Cells(3, .Columns.Count).End(xlToLeft).Offset(0, 1).Resize(rangeCopy.Rows.Count, Addcol)

    rangeCopy.Copy Destination:=rangePaste
    rangePaste.EntireColumn.ColumnWidth = rangeCopy.EntireColumn.ColumnWidth

End With

Upvotes: 0

Related Questions