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

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,154
Office Version
  1. 2016
Platform
  1. Windows
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.
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,563
Office Version
  1. 2013
Platform
  1. Windows
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
 

vcoolio

Well-known Member
Joined
Jun 29, 2014
Messages
1,079
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.
 

Natman111

New Member
Joined
Apr 26, 2021
Messages
14
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,563
Office Version
  1. 2013
Platform
  1. Windows
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.
 

Natman111

New Member
Joined
Apr 26, 2021
Messages
14
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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: 6
  • Sheet1.JPG
    Sheet1.JPG
    89.6 KB · Views: 7
  • Master.JPG
    Master.JPG
    57.2 KB · Views: 7

Natman111

New Member
Joined
Apr 26, 2021
Messages
14
Office Version
  1. 365
Platform
  1. Windows
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
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,563
Office Version
  1. 2013
Platform
  1. Windows
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
 

Natman111

New Member
Joined
Apr 26, 2021
Messages
14
Office Version
  1. 365
Platform
  1. Windows
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).
 

Watch MrExcel Video

Forum statistics

Threads
1,129,552
Messages
5,636,975
Members
416,953
Latest member
broexc

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
Top