Copy row if cell contains specific word

[asaf27064]

Heĺlo
I have 2 sheets with one table in each of them. The sheets names is "Work" and "Bank".
In each table I want to copy the whole row in the table if cell in column B contain the word "Exist" and move it to a sheet in a new excel file (workbook) under the name of the relevant sheet.

 

[NMa91]

Please place below code to output file. First in Workbook open event and second to module.

Private Sub Workbook_Open()
CopyRow
End Sub

Sub CopyRow()
Dim arr() As Variant
Dim lRow As Long, sht As Worksheet, wb As Workbook, eRow As Long, inp As Workbook
Dim fPath As String
arr = Array("Work", "Bank", "Total")
Set wb = ThisWorkbook
fPath = ThisWorkbook.Path & "\Input.xlsx"
Set inp = Workbooks.Open(fPath, False)
For j = LBound(arr) To UBound(arr)
    Set sht = inp.Sheets(arr(j))
    lRow = sht.Range("A" & Rows.Count).End(xlUp).Row
    eRow = wb.Sheets(arr(j)).Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Row
    For i = 1 To lRow
        If sht.Range("B" & i) = "Exist" Then sht.Range("B" & i).EntireRow.Copy wb.Sheets(arr(j)).Range("A" & eRow)
        lRow = sht.Range("A" & Rows.Count).End(xlUp).Row
        eRow = wb.Sheets(arr(j)).Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Row
    Next i
Next j
End Sub

 

[asaf27064]

Please place below code to output file. First in Workbook open event and second to module.

Private Sub Workbook_Open()
CopyRow
End Sub

Sub CopyRow()
Dim arr() As Variant
Dim lRow As Long, sht As Worksheet, wb As Workbook, eRow As Long, inp As Workbook
Dim fPath As String
arr = Array("Work", "Bank", "Total")
Set wb = ThisWorkbook
fPath = ThisWorkbook.Path & "\Input.xlsx"
Set inp = Workbooks.Open(fPath, False)
For j = LBound(arr) To UBound(arr)
    Set sht = inp.Sheets(arr(j))
    lRow = sht.Range("A" & Rows.Count).End(xlUp).Row
    eRow = wb.Sheets(arr(j)).Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Row
    For i = 1 To lRow
        If sht.Range("B" & i) = "Exist" Then sht.Range("B" & i).EntireRow.Copy wb.Sheets(arr(j)).Range("A" & eRow)
        lRow = sht.Range("A" & Rows.Count).End(xlUp).Row
        eRow = wb.Sheets(arr(j)).Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Row
    Next i
Next j
End Sub

-Thank you very much buddy but it gives me "Runtime error 9 subscript out of range" What can i do?

 

[NMa91]

Ensure to have Work, Bank,Total sheets created in input & output file. Also, please confirm on which line runtime error occurs.

 

[asaf27064]

Ensure to have Work, Bank,Total sheets created in input & output file. Also, please confirm on which line runtime error occurs.

now its working but not automatic and the problem is that it doesnt copy the columns and create tables from the input file as well
and every time i launch the macro it add the same data below the existing data

 

[asaf27064]

Its working but not updating automatically and every time i run it,its add more rows with the same data and not updating the data on the output file
all i want is that when i open the output file it will auto update with all the rows the contains exist in column b but i want it to copy it with the columns from the input file meaning it will create tables with values.

 

[NMa91]

Double click thisworkbook and add it to that module. So that it will auto update.

Private Sub Workbook_Open()
CopyRow
End Sub

 

[asaf27064]

Double click thisworkbook and add it to that module. So that it will auto update.

Private Sub Workbook_Open()
CopyRow
End Sub

ok you can see in that pic:
https://imgur.com/a/w5NNh

its added the same data under the current one after i saved the output file and also doesnt have a columns only data.
can you fix it?

thank you!

 

[asaf27064]

Hi.im really sorry to bother you again but i would like to get help with this please
thank you very much!

 

[asaf27064]

Double click thisworkbook and add it to that module. So that it will auto update.

Private Sub Workbook_Open()
CopyRow
End Sub

Can you help me please?

 

[asaf27064]

ok you can see in that pic:
https://imgur.com/a/w5NNh

its added the same data under the current one after i saved the output file and also doesnt have a columns only data.
can you fix it?

thank you!

please help me