Insert Or Delete Columns Based On User Input

[HDMI]

Hello forum,

I'm working with this macro to add and delete columns base on a number entered into a cell on another spreadsheet. Please see attachment template example and code.

On the example attachment on the "Employees" sheet there are numbered columns starting from 1 thru 6 at columns H. I need a macro that will add more columns or delete columns indicated by the number inputted in cell C9 on the "Monitoring Info" sheet. For instance I input the number 50 in the "Monitoring Info" sheet in cell C9 the "Employees" sheet will show starting from column H the number of columns 1 Thru 50.
The column will be incremented or diminish based on the user input on the Monitoring Info page cell C9. If the user input 5 into cell C9 then only 5 column will show numbered 1 through 5 starting at column H1 on the "Employee" sheet. If the user inputs 10 into cell C9 the number columns will show 1 thru10 starting at H1 on the Employee" sheet. etc, etc. I'm new to this forum I'm trying to learn all this and the people that know how to do this like yourself I think are very smart and intelligent and I hope to learn also. Thank you all once again.

	
	
Sub Columninput()
Dim MyRange As Object
    ' Store the selected range in a variable.
    Set MyRange = Selection
    ' Select the entire column.
    Selection.EntireColumn.Select
    ' Insert Columns in all selected sheets.
    Selection.Insert
    ' Reselect the previously selected cells.
    MyRange.Select

End Sub

[Excel Fox]

hi HDMI, welcome to Excel Fox

Yes, this can be done. However, you need to mention whether you'll be doing that on the empty template, or whether the data range will be filled. So for example, if you already have information filled in say H to M columns, and then you say you want to insert 10 columns, then the remaining 4 columns would need to be entered 'BETWEEN' one of column H:M, to maintain consistent formatting. Otherwise, a lot more code will need to be written for maintaining format also. So can you clarify whether you'll be running the code on an empty template, or a filled one

[HDMI]

Thank you so much for responding. The Template will be filled and you are correct the last four column will always have to be include whether if it is one column or 5000 hopefully I will never have 5000 but just in case. :-) Thank you again!!

[Excel Fox]

Here's the code. By the way, you need to remove the trailing space in the Employees sheet tab. Otherwise, the code below will throw an error.

Sub Columninput()

    Dim lng As Long
    Dim lngTotalEmployees As Long
    Dim lngCol As Long
    
    For lngCol = 8 To Worksheets("Employees").Cells(1, Columns.Count).End(xlToLeft).Column
        lngTotalEmployees = lngTotalEmployees + 1
        If Worksheets("Employees").Cells(1, lngCol).Value <> lngTotalEmployees Then
            Exit For
        End If
    Next lngCol
    lngTotalEmployees = lngTotalEmployees - 1
    lng = Worksheets("Monitoring Info.").Range("C9").Value
    If lng > lngTotalEmployees Then
        For lngCol = 1 To lng - lngTotalEmployees
            Worksheets("Employees").Columns(8 + lngTotalEmployees - 1).Insert xlToRight
            Worksheets("Employees").Cells(1, 8 + lngTotalEmployees - 1).Value = lngTotalEmployees + lngCol
        Next lngCol
        With Worksheets("Employees").Sort
            .SortFields.Clear
            .SortFields.Add Key:=.Parent.Range("H1").Resize(, lng), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
            .SetRange .Parent.Range("H1").Resize(, lng)
            .Header = xlGuess
            .MatchCase = False
            .Orientation = xlLeftToRight
            .SortMethod = xlPinYin
            .Apply
        End With
    ElseIf lng < lngTotalEmployees Then
        Worksheets("Employees").Columns(8 + lng).Resize(, lngTotalEmployees - lng).Delete xlToLeft
    Else
        MsgBox "No change!", vbOKOnly, "Delete Employee Columns"
    End If
    
End Sub

[HDMI]

Excel Fox this is excellent!!! Thank you so much. Outstanding!!!