Edit this macro

[Kent48]

Hi,

I have a recorded macro, here is the code

Sub Test()

ActiveCell.FormulaR1C1 = _

"=IF(LEFT(RC[-3],4)=""Item"",TRIM(SUBSTITUTE(LEFT(RC[-3],8),""-"","""",1)),RC[-3])"
Range("d2").Select
Selection.AutoFill Destination:=Range("d2:d3000")
Range("d2:d3000").Select
Workbooks.Open Filename:="C:\Test\Test Price List.xls"

Range("e2").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-1],'[Test Price List.xls]Sheet1'!C1:C5,5,FALSE)"
Range("e2").Select
Selection.AutoFill Destination:=Range("e2:e3000")
Range("e2:e3000").Select
Cells.Select
Selection.Sort Key1:=Range("e2"), Order1:=xlAscending
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End Sub


Here is what I want to do, I want to open a new workbook, then run th
macro to open a maste list (Test Price List.xls), then go back to th
first workbook, run the formula and autofill and sort it. The abov
does not work since when I opened the master list I select E2 but i
the wrong workbook, it should be in the first workbook.

Do I need to name the first workbook in the macro, then refer to
new name.xls and select E2?

Do I need to select all these cells/ranges or can it be done smarter?


Thanks in advance,

Kent Wilso

 

[Tom Ogilvy]

Sub Test()
Set sh = Activesheet
ActiveCell.FormulaR1C1 = _

"=IF(LEFT(RC[-3],4)=""Item"",TRIM(SUBSTITUTE(LEFT(RC[-3],8),""-"","""",1)),R
C[-3])"
Range("d2").Select
Selection.AutoFill Destination:=Range("d2:d3000")
Range("d2:d3000").Select
Set Sh = Activesheet
Workbooks.Open Filename:="C:\Test\Test Price List.xls"

Application.Goto Sh.Range("e2"), True
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-1],'[Test Price List.xls]Sheet1'!C1:C5,5,FALSE)"
Range("e2").Select
Selection.AutoFill Destination:=Range("e2:e3000")
Range("e2:e3000").Select
Cells.Select
Selection.Sort Key1:=Range("e2"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End Sub