title: Macro for adding a column with a specific header and populating a default value in all rows
body: <p>I have a requirement to add a new column next to the last header cell with data and fill the new column with some random value say "Cool !" in a given worksheet.</p>
  
  <p>Below is my macro:</p>
  
  <pre><code>Sub sbInsertingColumns(sourceFile As String, Worksheet As String)
  Dim lastColName As String
  Dim LastColumn As Long
      With ActiveSheet
          LastColumn = Workbooks(sourceFile).Worksheets(Worksheet).Range("A1").End(xlToRight).Column
      End With
      Dim nextCell As Integer
      nextCell = Val(LastColumn) + Val(1)
  
      lastColName = Replace(Replace(Cells(1, nextCell).Address, "1", ""), "$", "")
      Workbooks(sourceFile).Worksheets(Worksheet).Range(lastColName &amp; 1).Value = "New Header"
      MsgBox "New Column has been added : [ " &amp; lastColName &amp; 1 &amp; "]"
  End Sub
  </code></pre>
  
  <p>Adding values :</p>
  
  <pre><code>Sub Add()
      Dim cellVal As String
      Dim cell As Range
      cellVal = .Range("E")
      For Each cell In Sheets("Sheet1").Range("E:e").SpecialCells(xlCellTypeConstants, xlNumbers)
          cell.Value = "Cool !"
      Next cell
  End Sub
  </code></pre>
  
  <p>I can able to add a new column with some header , but the above logic for adding value into the new column is not working. Can anyone please help me out.
  Thanks for your time!</p>