Macro to insert a column and fill that column with word "January"

Hi Can anyone please help?

I need a macro so that it inserts a column between column A & B and then all cells in new column filled by word "January".

I tried recording macro but the problem is that it fills all cell in column B upto cell B65000. I need to fill cell only upto where there's data on column A so if column A has data upto 6000 then only upto B6000.

Any help much appreciated.

Hi aka189,

You need to learn to find the last row in a column. Try this code. It isn't cute as it is mostly a recorded macro but I've added a LastRow variable to do what you want.

Sub JanInColB()
    
    Dim LastRow As Double
    LastRow = Cells(Rows.Count, "A").End(xlUp).Row
    Columns("B:B").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("B2").Select
    ActiveCell.FormulaR1C1 = "January"
    Range("B2").Select
    Selection.Copy
    Range("B3:B" & LastRow).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
End Sub