Inserting columns and keeping formula in row 5, but clearing the contents below row 5

[pattishort]

I apologize in advance for the lengthy post, but it's better to have the detail up front.

Excel version: 2010
OS: Windows 7

GOAL: Insert additional columns, which are blank (except for the sequential number in row 5), in the table as needed. The sequential numbers in row 5 have a formula (=COLUMN(C$5)-2) and represent the procedure steps that are on a different worksheet of the workbook. Therefore, as the user does their testing and modify the procedure by adding steps, they should be able to insert additional columns to represent those steps.

STEPS to insert new columns:
1. Select a cell in a column to which to add new columns (columns are added after active column).
2. Click the Insert Columns button.
3. Enter number of columns to add after active column.
4. Click OK to add columns.

CURRENT BEHAVIOR: When the user clicks the Insert Columns button, new columns ARE added but the content from the active column (below row 5) is copied over to the new columns.

EXPECTED BEHAVIOR: When the user clicks the Insert Columns button, new columns are added and the contents below row 5 are cleared. This is the same concept as my Add Rows button, which adds the rows, keeps the formula, and clears the contents starting in column B.
I’ve spent quite a bit of time searching the internet for answers on how to get this to work. I’ve tried different things with the code and have had some interesting results. For example, if 1 column is added the column is added before the active column with the contents cleared. Or if I want to add 3 columns a single column is added before the active column but the remaining columns are added after the active column with the contents of the active column. In other words, everything “I” have tried hasn’t worked successfully.

I think I’ve isolated the portion of the code that is giving me heartburn, but I’m not 100% positive.

QUESTION: What is it that I’m missing in the code to insert columns and clearing the content below row 5?

Sub InsertColumnsAndFillFormulas_caller()  '-- this macro shows on Tools, Macro..., Macros (Alt+F8) dialog
  Call InsertColumnsAndFillFormulas
End Sub


Sub InsertColumnsAndFillFormulas(Optional vCols As Long = 0)
     ' Column selection based on active cell
    Dim x As Long
    
    ActiveCell.EntireColumn.Select 'So you do not have to preselect entire column
    
    If vCols = 0 Then
        vCols = Application.InputBox(prompt:= _
        "How many columns do you want to add?", Title:="Add Columns", _
        Default:=1, Type:=1) 'Default for 1 column, type 1 is number
        If vCols = False Then Exit Sub
    End If
    
    Dim Sht As Worksheet, shts() As String, i As Long
    ReDim shts(1 To Worksheets.Application.ActiveWorkbook.Windows(1).SelectedSheets.Count)
    i = 0
    
    For Each Sht In Application.ActiveWorkbook.Windows(1).SelectedSheets
        Sheets(Sht.Name).Select
         '---------------------------------------------------------------------------
        ActiveSheet.Unprotect Password:="justme"
         '---------------------------------------------------------------------------
         
        i = i + 1
        shts(i) = Sht.Name
        x = Sheets(Sht.Name).UsedRange.Columns.Count 'lastcell fixup
        
      '---------------------------------------------------------------------
      'having issues with the following code -- I need to be able to clear the contents in the added column after
      'it's been added, except for the formulas in row 5.


       Selection.Resize(Columnsize:=2).Columns(2).EntireColumn.Resize(Columnsize:=vCols).Insert Shift:=xlToRight
    
       Selection.AutoFill Selection.Resize(Columnsize:=vCols + 1), xlFillDefault
    
       On Error Resume Next
       Selection.Offset(-1).Resize(Val(vCols)).EntireColumn.SpecialCells(xlConstants).ClearContents
            
       '-------------------------------------------------------------------------------------------------------------------------
         
        '---------------------------------------------------------------------------
        ActiveSheet.Protect Password:="justme", DrawingObjects:=False, contents:=True, Scenarios:=True, _
        AllowFormattingCells:=True, AllowFormattingRows:=True, _
        AllowInsertingColumns:=True, AllowDeletingColumns:=True, AllowInsertingRows:=True, _
        AllowInsertingHyperlinks:=True, AllowDeletingRows:=True
         '---------------------------------------------------------------------------
         
    Next Sht
    Worksheets(shts).Select
End Sub

 

[Tubal]

Hi Patti,

Excellent comments in your code!

How about right after you ".ClearContents" you follow up with setting the formula in row 5 with this http://www.mrexcel.com/forum/excel-...-formula-using-visual-basic-applications.html?

Tubal