Edit this macro
 

Thanks a lot, I appreciate your help


Kent




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

--
Regards,
Tom Ogilvy


"Kent48" wrote in message
...

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)),R
C[-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 ru
the
macro to open a maste list (Test Price List.xls), then go back t
the
first workbook, run the formula and autofill and sort it. The above
does not work since when I opened the master list I select E2 but in
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 don
smarter?


Thanks in advance,

Kent Wilson


--
Kent48

------------------------------------------------------------------------
Kent48's Profile:
http://www.excelforum.com/member.php...o&userid=14919
View this thread
http://www.excelforum.com/showthread...hreadid=265740


--
Kent4
-----------------------------------------------------------------------
Kent48's Profile: http://www.excelforum.com/member.php...fo&userid=1491
View this thread: http://www.excelforum.com/showthread.php?threadid=26574