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