We help IT Professionals succeed at work.

EXCAL VBA

Hi,

Attached DEMO.XLS , first highlight  by manual  Sheet  PI  A3: H5,  then run  copydata marco ,  please help me  to  amend the  copydata  marco  ( refer to  Sheet PRICE   Column  REF_NO
 ,  to   get the result  as 1.JPG)


Stanley
DEMO.xls
1.JPG
Comment
Watch Question

Top Expert 2008

Commented:
Here is my version of it.

Thomas

Sub copydata()
Dim wbk As Workbook
Set wbk = ActiveWorkbook

    Selection.Copy
    Workbooks.Add
    ActiveSheet.Paste
    Selection.Offset(0, Selection.Columns.Count).Resize(, 1) = "IC3"

wbk.Activate
End Sub

Open in new window

Author

Commented:
Thomas,

Your code  only  fill  in  IC3  ,  my desired  result should be lookup  the Sheet PI   REF_NO
 and Sheet PRICE   REF_NO   to find out the VEND_NO.


e.g. REF_NO   1002   should be  103

REF_NO  1006  should be 102 ..........................................................
Top Expert 2008

Commented:
So take the    REF_NO  from the selection, then look for it in the PRICE Tab, and return the    VEND_NAME corresponding to the    V_ITEM_NO?

Author

Commented:
Yes right !
Top Expert 2008
Commented:
Try this, it should do it.

Thomas

Sub copydata()
Dim wbk As Workbook
Set wbk = ActiveWorkbook

    Selection.Copy
    Workbooks.Add
    ActiveSheet.Paste
    Selection.Offset(0, Selection.Columns.Count).Resize(, 1).FormulaR1C1 = _
        "=INDEX([" & wbk.Name & "]PRICE!C2,MATCH(text(RC[-7],""0""),[" & wbk.Name & "]PRICE!C1,0))"
    
    Selection.Offset(0, Selection.Columns.Count).Resize(, 1).Value = _
        Selection.Offset(0, Selection.Columns.Count).Resize(, 1).Value
        
    wbk.Activate
End Sub

Open in new window