Edit this macro
 

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

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

Edit this macro
 

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 run the
macro to open a maste list (Test Price List.xls), then go back to 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 done 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