Thomas Kuriakose
Active Member
Dear Sir,
We have a worksheet in which we need to automate the number of column entries based on the number of units required.
The cells in each row have formulas based on input parameters and subtotals. The requirement is to insert defined number of columns based on the units required (can be input box or cell reference) which should result in copying all formulas and formats to all columns inserted and also changing the total column value.
The inserted columns should be before the last three columns namely Total, Budget and Variance and after column E.
For example if the number of units entered in C3 is 4, then the number of columns inserted from column F should be 4 until column I. The Total Column, Budget and Variance should come after Column I
The below code was provided by Thau Theme Sir and it is working if the column of Units is just after C3, but since there are additions of columns to the sheets it is not working now.
Kindly help with the correct code for this.
Thank you so much,
with regards,
thomas
We have a worksheet in which we need to automate the number of column entries based on the number of units required.
The cells in each row have formulas based on input parameters and subtotals. The requirement is to insert defined number of columns based on the units required (can be input box or cell reference) which should result in copying all formulas and formats to all columns inserted and also changing the total column value.
The inserted columns should be before the last three columns namely Total, Budget and Variance and after column E.
For example if the number of units entered in C3 is 4, then the number of columns inserted from column F should be 4 until column I. The Total Column, Budget and Variance should come after Column I
The below code was provided by Thau Theme Sir and it is working if the column of Units is just after C3, but since there are additions of columns to the sheets it is not working now.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim LC As Integer
Dim I As Integer
Application.ScreenUpdating = False
If Target.Address <> "$C$3" Then Exit Sub
If Target.Value = "" Or Target.Value = 0 Then MsgBox "Value must be superior to 0": Exit Sub
LC = Cells(2, Application.Columns.Count).End(xlToLeft).Column
If LC = 5 Then
If Target.Value = 1 Then
Exit Sub
Else
For I = 2 To Target.Value
Columns(4).Copy
Columns(I + 3).Insert Shift:=xlToRight
Cells(2, I + 3).Value = "Unit " & I
Cells(3, I + 3) = I
Next I
End If
Application.CutCopyMode = False
Exit Sub
End If
Range(Cells(2, 5), Cells(2, LC - 1)).EntireColumn.Delete
For I = 2 To Target.Value
Columns(4).Copy
Columns(I + 3).Insert Shift:=xlToRight
Cells(2, I + 3).Value = "Unit " & I
Cells(3, I + 3) = I
Next I
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub
Kindly help with the correct code for this.
Thank you so much,
with regards,
thomas