Find Cell from another workBook

[Nicole1023]

Hi,

I have to open another work book to get a certain cell with a value.
ex. in my source file (prod plan) i have columns A,B,C,D. in column B it contains Line1 up to Line10. In column C it contains Model and Column D it contains Quantity.

Prod Plan workbook

Sample:
A-----B-----C------D
---------------------
AAA--Line1--M1----87
BBB--Line2--L2-----45
CCC--Line3--X1----0
up to Line 10

I need to find all Lines (column b) as my reference to get the column D records using Excel Macro and paste or copy to another worksheet. Is this posible to use the IF to get the data from Column D or Looping. Any idea guys on how to make this in Excel Macro. THanks.

Desired Result:

Line1|87

 

[Nicole1023]

Can you help me guys to modify my macro codes to come up with my desired result. thanks.

Sub SearchForString()
    Dim ws As Worksheet
    Dim wb As Workbook
    Dim lr As Long
    
    Dim LSearchRow As Integer
    Dim LCopyToRow As Integer
   
    On Error GoTo Err_Execute
    Set wb = ActiveWorkbook
    Set ws = ActiveSheet
    'Open Data Source
    Workbooks.Open Filename:="C:\Production Dashboard\Production Plan"
  
   'Start search in row 10 if strings Line1 to Line10 is exist
    LSearchRow = 10
   
   'Start copying data to row 2 in Sheet2 (row counter variable)
   ' LCopyToRow = 12
   
   
   
    While Len(Range("B" & CStr(LSearchRow)).Value) > 0
   
      'If value in column B = "Line1 and So on ", get the value of column D that correspond the Lines
      If Range("B" & CStr(LSearchRow)).Value = "Line2" Then
      
         'Select row in Sheet1 to copy
         'Rows(CStr(LSearchRow) & ":" & CStr(LSearchRow)).Select
         'Selection.Copy
         
         'Paste the value in Sheet2
         Sheets("Sheet2").Select
         Rows(CStr(LCopyToRow) & ":" & CStr(LCopyToRow)).Select
         ActiveSheet.Paste
         
         'Move counter to next row
         LCopyToRow = LCopyToRow + 1
         
         'Go back to Sheet1 to continue searching
         Sheets("Sheet1").Select
         
      End If
      
      LSearchRow = LSearchRow + 1
      
   Wend
   
   'Position on cell A3
   Application.CutCopyMode = False
   Range("B10").Select
   
   MsgBox "All matching data has been copied."
   
   Exit Sub
   
Err_Execute:
   MsgBox "An error occurred."
   
End Sub

 

[Andrew Poulsom]

You could AutoFilter column B for "Line1" and copy the visible cells - SpecialCells(xlCellTypeVisible).

 

[Nicole1023]

I need to find the strings in Column B (composed of Line1, Line2 and so on) then copy the value of column D to Sheet 2 as my working File.

Sheet1
Sample:
A-----B-----C------D
---------------------
AAA--Line1--M1----87
BBB--Line2--L2-----45
CCC--Line3--X1----0
up to Line 10

Sheet2
Column
E---------F
Line1----87
Line2----45
Line3----0

end so on..