Inserting columns based on keycells value issue

[Tdorman]

I am trying to have the cells that are inserted be based on the keycells range. I had everything working properly but had massive delays when I tried to insert a substantial amount of columns. I then switched methods in inserting the columns which drastically speed up load times, from 45+mins to under 3mins.

The issue now is that when the columns are inserted it is not representative of the keycells range. If I try and add in 2 columns, because one is static and always there, I need it to add only one. I achieved this, however, if I need to update the amount of columns being inserted to say 3, when I update the amount it adds in 2 new columns bringing my total to 4 columns being displayed on the sheet. If I update the columns to 3, as in this example, I need 3 columns in total to be displayed on the appropriate sheet, not 4.

I understand that

.Columns(j + 1).Resize(, argColNum - 1).Insert

is adding in one less than the

argColNum

so it will continue to add in one less than the keycells range. How do I limit the amount of columns that are added or updated to only reflect the keycells range? (If I update from 2 to 3, only show three columns, not insert an additional 2) And can it also keep the functionality of inserting all at once?

Private Sub Worksheet_Change(ByVal Target As Range)
                                                                                                                                             
    Dim KeyCells As Range, colNum As Long
    Dim ws As Worksheet

    Application.ScreenUpdating = False
    
        SOMESHEETS = "*C-Proposal-19*MemberInfo-19*Schedule J-19*NOL-19*NOL-P-19*NOL-PA-19*Schedule R-19*Schedule A-3-19*Schedule A-19*Schedule H-19*"
        Set KeyCells = Range("B30")
        If Not Application.Intersect(KeyCells, Target) Is Nothing Then
            If IsNumeric(KeyCells.Value) Then
                colNum = KeyCells.Value
                If colNum > 0 Then
                 For Each ws In ThisWorkbook.Worksheets
                     If ws.Visible = xlSheetVisible Then
                     If CBool(InStr(LCase(SOMESHEETS), LCase("*" & ws.Name & "*"))) Then
                            InsertColumnsOnSheet argSheet:=ws, argColNum:=colNum
                     End If
                     End If
                 Next ws
                End If
            End If
        End If
        

    
    SOMESHEETS = "*MemberInfo-20*C-Proposal-20*Schedule J-20*NOL-20*Schedule R-20*NOL-P-20*SchA-3-20*Schedule H-20*NOL-PA-20*Schedule A-20*Schedule A-5-20*"
    Set KeyCells = Range("B36")
    If Not Application.Intersect(KeyCells, Target) Is Nothing Then
        If IsNumeric(KeyCells.Value) Then
            colNum = KeyCells.Value
            If colNum > 0 Then
                For Each ws In ThisWorkbook.Worksheets
                    If ws.Visible = xlSheetVisible Then
                    If CBool(InStr(LCase(SOMESHEETS), LCase("*" & ws.Name & "*"))) Then
                            InsertColumnsOnSheet argSheet:=ws, argColNum:=colNum
                    End If
                    End If
                Next ws
            End If
        End If
    End If
    
    Application.ScreenUpdating = True
End Sub

This is the code that goes into a module. If I run it twice the

If TotalCol > LeftFixedCol + argColNum + 1 Then

portion of code will reduce the amount of column back to what the keycells range value is.

Option Explicit
Public Sub InsertColumnsOnSheet(ByVal argSheet As Worksheet, ByVal argColNum As Long)
    
    Dim rng As Range, c As Range
    Dim TotalCol As Long, LeftFixedCol As Long
    Dim i As Long
    Dim ws As Worksheet
    Dim j As Integer, k As Integer
    
    Set ws = Worksheets("C-Proposal-20")
    With argSheet
        Set rng = .Range(.Cells(1, 6), .Cells(1, .Columns.Count))
        Set c = rng.Find("TOTAL")
        If Not c Is Nothing Then
            TotalCol = c.Column
            LeftFixedCol = 5
            j = .Range("B4").End(xlToRight).Column
            If TotalCol < LeftFixedCol + argColNum + 1 Then
                    .Columns(j).Copy
                    .Columns(j + 1).Resize(, argColNum - 1).Insert CopyOrigin:=xlFormatFromLeftOrAbove
                        Application.CutCopyMode = False
            End If
            If TotalCol > LeftFixedCol + argColNum + 1 Then
                For i = TotalCol - 1 To LeftFixedCol + argColNum + 1 Step -1
                    .Columns(i).Delete
                Next i
            End If
        End If
    End With

For reference this code achieved the desired result of only displaying the amount of columns on the appropriate sheet as determined by the keycells value. (if I had 2 columns originally and increased it to 3 then 3 would be displayed not having it add 2 and display 4)

Set ws = Worksheets("Schedule J-20")
    With argSheet
        Set rng = .Range(.Cells(1, 4), .Cells(1, .Columns.Count))
        Set c = rng.Find("FINISH")
        If Not c Is Nothing Then
            TotalCol = c.Column
            LeftFixedCol = 3
            If TotalCol < LeftFixedCol + argColNum + 1 Then
                For i = TotalCol To LeftFixedCol + argColNum
                    .Columns(4).Copy
                    .Columns(5).Resize().Insert CopyOrigin:=xlFormatFromLeftOrAbove
                        Application.CutCopyMode = False
                Next i
                End If
            If TotalCol > LeftFixedCol + argColNum + 1 Then
                For i = TotalCol - 1 To LeftFixedCol + argColNum + 1 Step -1
                    .Columns(i).Delete
                Next i
            End If
        End If
    End With

 

[Tdorman]

Update:

I added in

 k = j - LeftFixedCol

and changed

.Columns(j + 1).Resize(, argColNum - k).Insert

Fix seems to be working and load times are still the same