Paste in the first row with same value from other workbook

[mdorey]

Hello all,

i'm really stuck on this one.

I have the following code...

Sub TesteSelect()

Dim myValue As Variant
myValue = InputBox("Give me some input")
Application.ScreenUpdating = False
Application.DisplayAlerts = False
ChDir "L:\..."
'1
    Workbooks.OpenText Filename:= _
        "L:\...\PRO " & myValue & ".txt" _
        , Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
        xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
        Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(1, 1), _
        TrailingMinusNumbers:=True
        
                Windows("pro " & myValue & ".txt").Activate
                Rows("2:" & Range([A1], ActiveSheet.UsedRange).Rows.Count - 2).Copy
                Windows("MASTER FILE_" & myValue & ".xlsm").Activate
                Worksheets("pro").Activate
        
        'paste CODE
        'paste CODE
        'paste CODE
        'paste CODE
        
        'Windows("pro " & myValue & ".txt").Close

Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub

Until now is fine and working very nicely for my level of skills with VBA...

but now i want to past what was copy from the opened file into the first line (column B ) that has the same value from the file opened “B2”…

Basically I need to find a match between the MASTER FILE (that open the other file) unknown row but column B and the OPENED FILE “B2” to set the destination to paste…

I hope I’ve made my self-clear on this one and I really appreciate any help or suggestions…

 

[mdorey]

Even without having help from this forum i share here my solution for anyone who what to adapt for their needs

Sub Final_Find_and_Past() 
    Application.ScreenUpdating = False 
    Application.DisplayAlerts = False 
    Dim myValue As Variant 
    myValue = InputBox("Which Date?! Format:ddmmyy") 
     
    Dim wb1 As Workbook 
    Set wb1 = ThisWorkbook 
     
     
    Dim rFind As Range 
    Dim wb2 As Workbook, ws2 As Worksheet 
    Dim wb3 As Workbook, ws3 As Worksheet 
    Dim wb4 As Workbook, ws4 As Worksheet 
    Dim wb5 As Workbook, ws5 As Worksheet 
    Dim wb6 As Workbook, ws6 As Worksheet 
    Dim wb7 As Workbook, ws7 As Worksheet 
    Dim ws As Worksheet 
    Dim FoundCell As Range 
    Dim rLookRange As Range 
     
     
    Set ws2 = wb1.Sheets("PRO") 'ActiveSheet
    Set wb2 = Workbooks.Open("L:\...\pro " & myValue & ".txt") 
    Set rFind = wb1.Sheets("pro").Columns(2).Find(What:=wb2.Sheets("pro " & myValue).Range("B2"), _ 
    Lookat:=xlWhole, MatchCase:=False, SearchFormat:=False) 
    Set FoundCell = ws2.Range("B:B").Find(What:=rFind) 
    Set rLookRange = wb2.Sheets("pro " & myValue).Range("2:" & Range([A1], ActiveSheet.UsedRange).Rows.Count - 2) 
    ws2.Activate 
    rLookRange.Copy wb1.Sheets("pro").Rows(FoundCell.Row) 
    wb2.Close SaveChanges:=False 
    Set rFind = Nothing 
    Set FoundCell = Nothing 
    Set rLookRange = Nothing 
     
    ......copy paste the code For all the other files

This code will find the range that it's need (unknown range of rows) select the specific range from second row to the third last one and past on the right line.... every time is a different one...