VBA to find and reference a cell with date closest to today but not greater - set print area from that

[HRIS]

I have a spreadsheet with dates across row 1 and information related to the dates in the columns below. The cells are formatted to show month and year (so it shows July-12 for this month with the actual date entered being 7/1/12).

I want to auto set the print area, upon sheet activation, so that it sets the print area as the columns for the 12 previous months before the current month's column. So I was thinking I would set the print area range by starting with the cell that has a date < Date & >= Date - 28 then offsetting -12 columns and -86 rows. However, I am struggling with the code to find and reference the cell with the previous month's date as mentioned.

Any thoughts/experience you can share is appreciated!

Example, this month I would want to set the print area as the months & their columns that are in blue below:

7/1/11

8/1/11

9/1/11

10/1/11

11/1/11

12/1/11

1/1/12

2/1/12

3/1/12

4/1/12

5/1/12

6/1/12

7/1/12

123

120

127

129

129

135

137

141

138

131

140

129

136

1y

1y

3n

4y

8n

11n

12y

16y

13n

11n

12y

13n

13n

<TBODY>
</TBODY>

 

[texasalynn]

try this

Sub SetPrint()
Dim i, j, LC, LR, As Long
Dim PDate As Date
LR = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
LC = ActiveSheet.Cells(1, "IV").End(xlToLeft).Column
PDate = Date - 365
For i = 1 To LC
    If WorksheetFunction.Text(Cells(1, i), "mm-yyyy") = WorksheetFunction.Text(PDate, "mm-yyyy") Then
        StartA = Cells(1, i).Address
        StartE = Cells(1 + 86, i + 11).Address
        ActiveSheet.PageSetup.PrintArea = Range(StartA, Range(StartE)).Address
        GoTo 0
    End If
Next i
End Sub

 

[HRIS]

Thank you for taking a look at this!

On the line "GoTo 0" I am getting "Compile error: Label not defined" ?

 

[texasalynn]

that is weird the code seemed to have dropped that line

next i

0

End Sub

 

[HRIS]

I believe it is working wonderfully! Thank you so much for your help!!!