HI,
I need to insert a 2 new Columns into an existing worksheet but they have to fit in the correct place alphabetically.
I've got an input box that get the new Column's Title, but at the moment I can only add it to the next blank column at the end.
I need code that will take the input box text and check it against all the colums then insert a new colum in the right aphabetical place.
Here's the code I'm using which adds two columns at the end and formats them correctly.
Sub Add_LB()
'Find Last Column
Dim LastCol As Integer
With ActiveSheet
LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
End With
'Add Collumn Title
Dim myValue As Variant
myValue = InputBox("Enter title of new Learning Burst")
If myValue = "" Then GoTo Skip:
Cells(1, LastCol + 1).Select
ActiveCell.FormulaR1C1 = myValue
With ActiveCell
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = True
.Orientation = 90
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Font.Bold = True
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.499984740745262
.PatternTintAndShade = 0
End With
With Selection.Font
.ThemeColor = xlThemeColorDark1
.TintAndShade = 0
End With
' Add Follow Up Text in next column
Cells(1, LastCol + 2).Select
ActiveCell.FormulaR1C1 = "Follow Up"
Cells(1, LastCol + 2).Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = True
.Orientation = 90
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
Selection.Font.Bold = False
End With
Skip:
End Sub
Bookmarks