title: (Excel VBA) How to insert multi columns and cell.value for those columns into excel sheet before found cell?
body: <p><strong>1. Background &amp; purpose</strong><br>
  I have a Excel sheet with table of multi-columns. 
  I want to insert some columns(in yellow) via macro:</p>
  
  <ul>
  <li>"Total Cost" before "PIC" with formula: <code>Total Cost = Man-hour * C</code> 
  (where C is constant)</li>
  <li>"Reviewed.Date", "Reviewer", "Year" before "Month" and formula to determine fiscal year ("Year") based on "Inspected.Date".<br>
  Ex: <code>Cell of Year = YEAR("Inspected.Date")+IF(MONTH("Inspected.Date")&gt;=$D$1,1,0)</code></li>
  </ul>
  
  <p>Please refer to this picture for more detail.</p>
  
  <p><a href="https://i.stack.imgur.com/OomaZ.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/OomaZ.png" alt="Insert New Columns to Excel"></a></p>
  
  <p><strong>2. Problem &amp; questions</strong><br>
  I can loop through all columns and check if Column.header = "PIC" or "Month", then insert new columns. But I don't know how to insert "Column header" and "value of all cells in that column" with formula because after inserting new column, position of some columns will be changed.
  So, how can I do this with macro?
  Any helps would be highly appreciated.
  Thank you so much for your attention.</p>
  
  <p><strong>3. Here is my current VBA</strong></p>
  
  <pre><code>Sub InsertNewColumn()
     'Edit SPO Documents.
     'Consider worksheet"Sheet1"
  
     With Worksheets("Sheet1")
  
         '1. Find out last columns &amp; last row in target sheet.
          LastCol = .Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByColumns, _
                    SearchDirection:=xlPrevious).Column
          LastRow = .Cells.Find(What:="*", SearchOrder:=xlByRows, _
                    SearchDirection:=xlPrevious).Row        
         '2.Loop through all columns and check column header. 
         '  If column header is "PIC" or "Month", then insert new column
  
          For i = LastCol To 1 Step -1            
              On Error Resume Next            
              CellValue = .Cells(1, i)   'Get Column header   
  
              'Insert column "Total Cost"
              If CellValue = "PIC" Then
                  .Cells(1,i).EntireColumn.Insert Shift:=xlToLeft
                  .Cells(1,i).Value2 = "Total Cost" 'Add column header            
              End If
  
              'Insert column "Year"
              If CellValue = "Month" Then
                  .Cells(1,i).EntireColumn.Insert Shift:=xlToLeft
                  .Cells(1,i).Value2 = "Year" 'Add column header
  
                  'Add formula to year
                  With Sheets("Sheet1").Columns(i)
                      For j = LastRow To 2 Step -1                       
                          'Sheets("Sheet1").Cells(j, i).Formula = "=YEAR("Inspected.Date")+IF(MONTH("Inspected.Date")&gt;=$D$1,1,0)"
                          'I want to add formula to determine fiscal year for cell of Year based on "Inspected.Date"
                          'but I dont know how to do
                      Next j               
                  End With
              End If  
          Next i   
      End With
      Application.ScreenUpdating = True   
  End Sub
  </code></pre>