Insert multiple columns based on cell value

[Apple08]

Hi All

I need to insert columns 'Budget Hours' and 'Budget Cost' on the left of the column cells 'Planned Effort' and 'Planned Cost' respectively, and shift the data of Planned Effort and Planned Cost to the right as below:

Before insert
D3 Planned Effort
F3 Planned Cost
H3 Planned Effort
J3 Planned Cost
L3 Planned Effort
N3 Planned Cost
until the last column

After columns insert:
D3 Budget Hours (insert)
E3 Planned Effort (column data shift to the right from D to E)
G3 Budget Cost (insert)
H3 Planned Cost (column data shift to the right)
and so on...

So whenever the column cell is Planned Effort then insert a column Budget Hours on it's left, therefore Planned Effort column data shift to the right.
Whenever the column cell is Planned Cost, then insert a column Budget Cost on it's left, and Planned Cost column data shift to the right.

Any help is appreciated.

 

[Osvaldo Palmeiro]

Hi. Try:

Sub InsertColumns()
 Dim k As Long
  k = 4
  Application.ScreenUpdating = False
   Do
    If Cells(3, k) <> "" Then
     Columns(k).Insert
     Cells(3, k) = IIf(k Mod 2 = 0, "Budget Hours", "Budget Cost")
     Columns(k).AutoFit: k = k + 3
    Else: Exit Sub
    End If
   Loop
  Application.ScreenUpdating = True
End Sub

 

[Apple08]

Oh my goodness it works perfectly! Thanks Osvoldo,you are a genius! At first I was looking for something like if and then,it is something totally out of my expectation. Many thanks for that.

However I would like to add more columns, I wonder are you able to help me tounderstand how do they work. Is itpossible to update the code to add morecolumns like the below:
Beforeinsert
D3 Planned Effort

E3 ActualEffort
F3 Planned Cost
G3 ActualCost
so on until the last column

After columns insert:
D3 Budget Hours (insert)
E3 Planned Effort YTD (column data shift to the right, to add YTD to the text)
F3 ActualEffort YTD ((column data shift to the right, to add YTD to the text)
G3 Actual EffortYTD vs Budget % (insert)
H3 PlannedEffort Current (Insert)
I3 Actual EffortCurrent (Insert)
J3 BudgetCost (insert)
K3 PlannedCost YTD (column data shift to the right, to add YTD to the text)
L3 ActualCost YTD (column data shift to the right, to add YTD to the text)
M3 ActualCost YTD vs Budget % (insert)
N3 PlannedCost Current (insert)
O3 ActualCost Current (insert)
Then repeattill the last column.

Many thanksfor your help! I am really appreciated.

 

[Osvaldo Palmeiro]

Hi. See if the code below does what you need.

Sub InsertColumnsV2()
 Dim k As Long
  k = 4
  Application.ScreenUpdating = False
   Do
    If Cells(3, k) <> "" Then
     Columns(k).Insert
     Cells(3, k).Resize(, 3).Value = [{"Budget Hours","Planned Effort YTD","Actual Effort YTD"}]
     Columns(k).Resize(, 3).AutoFit: k = k + 3
     Columns(k).Resize(, 4).Insert
     Cells(3, k).Resize(, 6).Value = _
     [{"Actual Effort YTD vs Budget %","Planned Effort Current","Actual Effort Current","Budget Cost","Planned Cost YTD","Actual Cost YTD"}]
     Columns(k).Resize(, 6).AutoFit: k = k + 6
     Columns(k).Resize(, 2).Insert
     Cells(3, k).Resize(, 3).Value = [{"Actual Cost YTD vs Budget %","Planned Cost Current","Actual Cost Current"}]
     Columns(k).Resize(, 3).AutoFit: k = k + 3
    Else: Exit Sub
    End If
   Loop
  Application.ScreenUpdating = True
End Sub

 

[Apple08]

It works perfectly! Many thanks!