Inserting columns left of a variable column

mactoolsix

New member
Joined
Jul 11, 2014
Messages
2
Reaction score
0
Points
0
Location
Saratoga, CA
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

Super Moderator
Staff member
Joined
Dec 16, 2012
Messages
2,168
Reaction score
10
Points
38
Excel Version(s)
365
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
 
Top