Copy rows from one sheet to another based on text in one cell.

Natman111

New Member
Joined
Apr 26, 2021
Messages
14
Office Version
  1. 365
Platform
  1. Windows
I have a spreadsheet with multiple worksheets. I would like to automatically copy rows that are based on a criteria in Column A in all worksheets and then paste in one "Master" Sheet, then repeat for another criteria. Eg. I have 5 sheets, and in Column A in each I am wanting to find the text "MD2" then copy and paste the entire row into "Master" Sheet (on the next available row) - there could be 10 rows per sheet that could met this criteria. Then once this criteria has been completed repeat however this time look for the text "BM2". Hopefully that makes sense. Thank you in advance.
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
Try learn to use XL2BB to capture and paste your sample Master and one data sheet. By having sheet structure visually and sample data that can be copied and paste, it will help those helpers out there tremendously.
 
Upvote 0
Try this:
This script runs the code looking for "MD2"
When you want to search for "BM2"

Modify the script where shown to: BM2
And run the script again.
Or I could write script to use both. But for now lets see if this script works.

VBA Code:
Sub Filter_Me_Please()
'Modified  4/27/2021  1:33:03 AM  EDT
Application.ScreenUpdating = False
Dim lastrow As Long
Dim c As Long
Dim s As Variant
Dim i As Long
c = 1 ' Column Number Modify this to your need
s = "MD2" 'Search Value Modify to your need
lastrow = Cells(Rows.Count, c).End(xlUp).Row
Dim lastrowa As Long

For i = 1 To Sheets.Count
If Sheets(i).Name <> "Master" Then
lastrow = Sheets(i).Cells(Rows.Count, c).End(xlUp).Row

With Sheets(i).Cells(1, c).Resize(lastrow)
    lastrowa = Sheets("Master").Cells(Rows.Count, c).End(xlUp).Row + 1
    .AutoFilter 1, s
    counter = .Columns(c).SpecialCells(xlCellTypeVisible).Count
    If counter > 1 Then
        .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).EntireRow.Copy Sheets("Master").Cells(lastrowa, 1)
    Else
        'Do nothing
    End If
    .AutoFilter
End With
End If
Next
Application.ScreenUpdating = True
End Sub
 
Upvote 0
Hello Natman,

Another option is to place your criteria into an array:-
VBA Code:
Option Explicit
Sub Test()

        Dim ws As Worksheet, ar As Variant, wsM As Worksheet
        ar = Array("MD2", "BM2")
        Set wsM = Sheets("Master")
        
Application.ScreenUpdating = False
        
        wsM.UsedRange.Offset(1).Clear
        
        For Each ws In Worksheets
                If ws.Name <> "Master" Then
                        With ws.[A1].CurrentRegion
                                .AutoFilter 1, ar, 7
                                .Offset(1).EntireRow.Copy wsM.Range("A" & Rows.Count).End(3)(2)
                                .AutoFilter
                        End With
                End If
        Next ws

Application.ScreenUpdating = True

End Sub

The code assumes that you have headings in row1 of each source sheet with data starting in row2.
The "Master" sheet is cleared/refreshed each time the code is run.

I hope that this helps.

Cheerio,
vcoolio.
 
Upvote 0
Thanks for this. Is there a way to run the "MD2" then "BM2" as sometimes the BM2 will copy and paste first? Otherwise works well thank you
Did you try my script?
You could run the script and then change the one line of Code and then run the script again.
Or we allow you to enter MD2 into a Inputbox Or BM2 depending which one you want.
 
Upvote 0
Did you try my script?
You could run the script and then change the one line of Code and then run the script again.
Or we allow you to enter MD2 into a Inputbox Or BM2 depending which one you want.

Yes it did work. My wish is to have a drop down list with MD1,MD2,MD3 and so on then BM1,BM2,BM3 etc then run the code? I have attached some screen shots. Master has the drop down boxes in J1 and Q1
 

Attachments

  • Sheet2.JPG
    Sheet2.JPG
    86.7 KB · Views: 17
  • Sheet1.JPG
    Sheet1.JPG
    89.6 KB · Views: 18
  • Master.JPG
    Master.JPG
    57.2 KB · Views: 17
Upvote 0
Thanks "My Aswer Is This" was also thinking as I might end up with a large number of other sheets which will not have this data in are we able to just name which sheets to look in? if that helps
 
Upvote 0
Thanks "My Aswer Is This" was also thinking as I might end up with a large number of other sheets which will not have this data in are we able to just name which sheets to look in? if that helps
This is possible to specify sheets. And have drop down to select from.
But it depends on number of sheets. At least for me. There are others on the forum who can do a lot more then me.

So the drop down would be easy. But the sheet names may be a problem.
Are you talking about sheet name Alpha and Bravo and Echo and Mary and Jane and more and more.
for one value and another list of names for another value?
Are you wanting to run the script only once to do all these different possibilities
 
Upvote 0
This is possible to specify sheets. And have drop down to select from.
But it depends on number of sheets. At least for me. There are others on the forum who can do a lot more then me.

So the drop down would be easy. But the sheet names may be a problem.
Are you talking about sheet name Alpha and Bravo and Echo and Mary and Jane and more and more.
for one value and another list of names for another value?
Are you wanting to run the script only once to do all these different possibilities
Was going to have about 10 sheets overall. Out of the 10 sheets I was going to have Sheets being U1 U2 U3 U4 and U5. In each sheet U1 was going to have MD1 and BM1 and then in sheet U2 have MD2 and BM2 etc. I was then hoping to be able to mix and match as required ie sometime might need MD4 (which would be in sheet U4) and BM2 (in sheet U2).
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,192
Members
448,554
Latest member
Gleisner2

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