VBA to select column by name and set custom width

[wzthxj]

I'm using a script I found in a post from RickXL to auto-find the last column and add header name. This worked perfectly, and now I want to set the width of each new column to the values shown. I've tried with and without the Columns.EntireColumn.Select method, along with Columns.ColumnWidth = ** but no matter which combination I use, all columns are resized to 10. I'm a beginner and would appreciate any help possible. I also have some other actions that I would like to perform on the last 3 new columns which I've already created via another macro, but am unsure how to insert them into this one correctly. Thank you!

Sub InsertColumnAfterLastColumn()
Dim LastRow As Long
Dim LastCol As Long
Dim iRow As Long
Set ws = Sheet4

With ws
LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
.Cells(1, LastCol + 1).Value = "Observations"
Columns.EntireColumn.Select
Columns.ColumnWidth = 30

LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
.Cells(1, LastCol + 1).Value = "Deviations"
Columns.ColumnWidth = 20

LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
.Cells(1, LastCol + 1).Value = "Potential Debit Amount"
Columns.ColumnWidth = 10

LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
.Cells(1, LastCol + 1).Value = "Debit Amount"
Columns.ColumnWidth = 10

End With
End Sub

 

[Fluff]

How about

Sub wzthxj()
   
   With Sheet4.Cells(1, Columns.Count).End(xlToLeft).Offset(, 1).Resize(, 4)
      .Value = Array("Observations", "Deviations", "Potential Debit Amount", "Debit Amount")
      .ColumnWidth = 10
   End With
End Sub

 

[wzthxj]

Thanks for the reply! It looks like this will adjust all 4 to 10; what I need is 30, 20, 10, 10 due to content that will be added. Other thoughts?

 

[Fluff]

Missed that bit, try

Sub wzthxj()
   
   With Sheet4.Cells(1, Columns.Count).End(xlToLeft).Offset(, 1).Resize(, 4)
      .Value = Array("Observations", "Deviations", "Potential Debit Amount", "Debit Amount")
      .Resize(, 1).ColumnWidth = 30
      .Offset(, 1).Resize(, 1).ColumnWidth = 20
      .Offset(, 2).Resize(, 2).ColumnWidth = 10
   End With
End Sub

 

[wzthxj]

That worked perfectly, thank you! One last question; the new column "Deviations" contains a drop down menu in each cell. I need to somehow associate the drop-down with this column, with the caveat that the number of columns before the 4 new ones just created varies by user. I created a macro but it's currently associated with a specific cell (which will change). Thanks for your help.

Range ("**").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=Data!$A$2:$A$11"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With

 

[Fluff]

As this is a totally different question, it needs a new thread. Thanks