PDA

View Full Version : vlookup macro tricky



Pete
12-01-2009, 01:16 AM
Hi
Not sure how to amend the following vba code so the following happens:

1. I do not need to open work book 7 as it is already open, only open work book 3 when the macro runs.

2. Vlookup in book 7 column H against range in Book 3 Column A2:B100

column h book 7 is the same as column as in book 3

3. Insert the result into column I row 6

4. add auto filter

kindly assist...............

Sub Vlookup()

Dim strFilePath As String
Dim strFileName As String
Dim strNewFilePath As String
Dim strNewFileName As String

strFilePath = "U:\"
strFileName = "Book7.xls"
strNewFilePath = "U:\"
strNewFileName = "Book3.xls"
Columns("H:H").Select
Selection.Insert Shift:=xlToRight
Workbooks.Open Filename:=strNewFilePath & strNewFileName
Windows(strFileName).Activate
Range("H6").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],[" & strNewFileName & "]Sheet1!A2,2,FALSE)"
Range("H6").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Calculate
Columns("I:I").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("H6").Select
Windows(strNewFileName).Activate
ActiveWorkbook.Close
Windows(strFileName).Activate
End Sub

Bob Phillips
12-01-2009, 03:10 AM
So what do you get/not get?