Inserting columns left of a variable column

mactoolsix · Jul 11, 2014

I want to create a macro to insert 12 cells left of a column labeled "This Year"
As the spreadsheet grows (by 12 columns @ year) the "This Year" column moves to the right. Thus I need to reference the range off of that column and then insert 12 columns directly to the left of it each year. Can I somehow reference the label "This Year"?

I then need to enter the month labels in the new columns row 8.

My problem is trying to reference off the "This Year" column.

Can someone help me out with this?

Thank you!!!
Mike

p45cal · Jul 12, 2014

The following assumes This Year is on row 1 of the active sheet:

Code:

Sub blah()
Dim TY As Range
Set TY = [COLOR=#a52a2a]ActiveSheet[/COLOR].[COLOR=#ff0000]Rows(1)[/COLOR].Find(what:="This Year", lookat:=xlWhole, LookIn:=xlValues, searchformat:=False)
If Not TY Is Nothing Then
  TY.Resize(, 12).EntireColumn.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
[COLOR=#008000] 'delete next 4 lines if not wanted.[/COLOR]
  [COLOR=#0000ff]With TY.Offset(, -1)
    .Value = "Dec"
    .AutoFill Destination:=.Offset(, -11).Resize(, 12), Type:=xlFillMonths
  End With[/COLOR]
End If
End Sub

Inserting columns left of a variable column

mactoolsix

New member

p45cal

Super Moderator