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
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