Results 1 to 2 of 2

Thread: Inserting columns left of a variable column

  1. #1
    Neophyte mactoolsix's Avatar
    Join Date
    Jul 2014
    Location
    Saratoga, CA
    Posts
    2
    Articles
    0

    Inserting columns left of a variable column



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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

  2. #2
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,356
    Articles
    0
    Excel Version
    365
    The following assumes This Year is on row 1 of the active sheet:
    Code:
    Sub blah()
    Dim TY As Range
    Set TY = ActiveSheet.Rows(1).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
     'delete next 4 lines if not wanted.
      With TY.Offset(, -1)
        .Value = "Dec"
        .AutoFill Destination:=.Offset(, -11).Resize(, 12), Type:=xlFillMonths
      End With
    End If
    End Sub

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •