preseb
07-07-2011, 05:01 AM
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()
Dim strFile As String
Dim Tempfile As Workbook
Dim LR As Long
strFile = Application.GetOpenFilename
Workbooks.Open strFile
Set Tempfile = ActiveWorkbook
Sheets("IP").Select
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
Application.CutCopyMode = False
Tempfile.Close
End Sub
I did find this code, but instead of mm.dd.yy, i would like to enter Jan OP, Feb OP, Mar OP, etc
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
Thanks
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()
Dim strFile As String
Dim Tempfile As Workbook
Dim LR As Long
strFile = Application.GetOpenFilename
Workbooks.Open strFile
Set Tempfile = ActiveWorkbook
Sheets("IP").Select
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
Application.CutCopyMode = False
Tempfile.Close
End Sub
I did find this code, but instead of mm.dd.yy, i would like to enter Jan OP, Feb OP, Mar OP, etc
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
Thanks