Excelerating_
New Member
- Joined
- Jul 22, 2020
- Messages
- 1
- Office Version
- 365
- Platform
- Windows
Hello All,
First Post! Thank you for your time first and foremost.
I was able to add code to my spreadsheet that will insert a column after column "R" based on data value in column "L2"
The problem I am running into is developing a code that will add the formulas in column "R" across the new columns "S" "T" ,etc.
Any tips are greatly appreciated! My current VBA formula is below:
First Post! Thank you for your time first and foremost.
I was able to add code to my spreadsheet that will insert a column after column "R" based on data value in column "L2"
The problem I am running into is developing a code that will add the formulas in column "R" across the new columns "S" "T" ,etc.
Any tips are greatly appreciated! My current VBA formula is below:
VBA Code:
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range, ColNum As Long, TotalCol As Long, LeftFixedCol As Long
Dim Rng As Range, c As Range
Set KeyCells = Range("L2")
If Application.Intersect(KeyCells, Target) Is Nothing Then Exit Sub
If IsNumeric(KeyCells.Value) = False Then Exit Sub
ColNum = KeyCells.Value
If ColNum <= 0 Then Exit Sub
Set Rng = Range(Cells(4, 1), Cells(4, Columns.count))
Set c = Rng.Find("Total") 'the find is case senseticve, Change "Total" to desired key word to find
If c Is Nothing Then Exit Sub
TotalCol = c.Column
LeftFixedCol = 17 'Column A & B for Company and ID
Dim i As Integer
If TotalCol < LeftFixedCol + ColNum + 1 Then ' Add column
For i = TotalCol To LeftFixedCol + ColNum
Columns(i).Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
'Cells(3, i).Value = "" & i - LeftFixedCol ' may not use this line
Next i
End If
If TotalCol > LeftFixedCol + ColNum + 1 Then ' Add column
For i = TotalCol - 1 To LeftFixedCol + ColNum + 1 Step -1
Columns(i).Delete
Next i
End If
End Sub