Copy row if cell contains specific word

asaf27064

New Member
Joined
Jul 25, 2017
Messages
31
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.
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
Code:
Sub CopyRow()
Dim arr() As Variant
Dim lRow As Long, sht As Worksheet, wb As Workbook, eRow As Long


arr = Array("Work", "Bank")
Set wb = Workbooks.Add
wb.Sheets(1).Name = "Work"
wb.Sheets(2).Name = "Bank"


For j = LBound(arr) To UBound(arr)
    Set sht = ThisWorkbook.Sheets(arr(j))
    lRow = sht.Range("A" & Rows.Count).End(xlUp).Row
    eRow = wb.Sheets(1).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
 
Upvote 0
Code:
Sub CopyRow()
Dim arr() As Variant
Dim lRow As Long, sht As Worksheet, wb As Workbook, eRow As Long


arr = Array("Work", "Bank")
Set wb = Workbooks.Add
wb.Sheets(1).Name = "Work"
wb.Sheets(2).Name = "Bank"


For j = LBound(arr) To UBound(arr)
    Set sht = ThisWorkbook.Sheets(arr(j))
    lRow = sht.Range("A" & Rows.Count).End(xlUp).Row
    eRow = wb.Sheets(1).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 very much!
I want to add third sheet called "Total"
Can you add it to the formula?
 
Upvote 0
When I run the macro
It's shows an error:
Run-time error
Subscript out of range
 
Last edited:
Upvote 0
Code:
Sub CopyRow()
Dim arr() As Variant
Dim lRow As Long, sht As Worksheet, wb As Workbook, eRow As Long


arr = Array("Work", "Bank")
Set wb = Workbooks.Add
wb.Sheets(1).Name = "Work"
wb.Sheets(2).Name = "Bank"


For j = LBound(arr) To UBound(arr)
    Set sht = ThisWorkbook.Sheets(arr(j))
    lRow = sht.Range("A" & Rows.Count).End(xlUp).Row
    eRow = wb.Sheets(1).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

I want that it will create tables with the same columns in the new workbook and when I add a new row in the master workbook that match the criteria it will update the tables in the new workbook
And I have now third sheet called total so I want the formula to work on it too.
Thank you!
 
Upvote 0
Please can you share image for current data file and image for output data file. Do you require to add both sheets data(Work & Bank) combined into Total?
 
Upvote 0
Please can you share image for current data file and image for output data file. Do you require to add both sheets data(Work & Bank) combined into Total?

I'll explain my self better
I have now 3 sheets in one workbook that called "Work" "Bank" "Total" with table in each.

I want to create new workbook with the same table with the same sheets names
I want to output all the rows with the word "Exist" in column B to the new workbook under the same sheets name and just to create thrèe tables that contain rows with "exist" in 3 same sheets names and I want it the new workbook to update automatically if it's possible
I want that every time I add a row to table with the word "exist" in one of the three sheets so it will update it in the new workbook in the certain sheet that match.
Thank you very much!
 
Upvote 0
Please can you share image for current data file and image for output data file. Do you require to add both sheets data(Work & Bank) combined into Total?

Here's a screenshots:
This is my current Workbook which contains 3 sheets: "Bank", "Work", "Total".

Bank:
https://imgur.com/a/waWPs

waWPs

Work:
https://imgur.com/a/a1twY

Total:
https://imgur.com/a/NChm7

Now this how i want the output workbook to be:

Bank:
https://imgur.com/cTW7LS3

Work:
https://imgur.com/dK0pWFm

Total:
https://imgur.com/SdvzbZg

Also i want the output workbook to update automatically when i open it if its possible.
everytime i add a row with the word "Exist" in column B to one of the tables in the current workbook i want the output workbook to be auto updated.

Hope its all clear now i would like to get help with this buddy.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,549
Messages
6,114,264
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top