Macro to vlookup a different range each month

[psrs0810]

I need to create a macro that will do a vlookup from one workbook to another where the range will change every month.

How can I setup this macro so when there is a new month, the range changes?

This is what I have so far, but it does not handle the change for a new month.

Sub IPOPBudgetData()
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
Dim strFile As String
Dim Tempfile As Workbook
Dim LR As Long

strFile = Application.GetOpenFilename
Workbooks.Open strFile
Set Tempfile = ActiveWorkbook
<o> </o>
Sheets("IP").Select
<o> </o>
Windows("Allowance Macro.xls").Activate
Sheets("By_Trans_Code").Select
Range("E2").Select
LR = Range("A" & Rows.Count).End(xlUp).Row
With Range("E2:E" & LR)
.Formula = "=VLOOKUP(RC[-4],'[2011 Allowance Budget by Trans Code.xls]OP'!C3:C17,15,0)"
.Value = .Value
End With
<o> </o>
<o> </o>
<o> </o>
Application.CutCopyMode = False
Tempfile.Close
End Sub

 

[psrs0810]

I am looking to add something like this:


Sub test()
Dim varDate
varDate = Application.InputBox("Enter the date of the target workbook's name", "Enter Date", Format(Date, "mm.dd.yy"), , , , , Type:=2)
If IsDate(Replace(varDate, ".", "/")) Then
varDate = Format(CDate(Replace(varDate, ".", "/")), "mm.dd.yy")
Else
MsgBox "Invalid date entered - exiting sub!": Exit Sub
End If
Range("B1").FormulaR1C1 = "=VLOOKUP(RC[-1],'[PN " & varDate & ".xls]Sheet1'!C1:C2,2,0)"

End Sub

but instead of mm.dd.yy - I would like to enter Jan OP, Feb OP, Mar OP - etc.