How to copy rows from multiple worksheets based on criteria into a new sheet?
Supposing, you have a workbook with three worksheets which have the same formatting as below screenshot shown. Now, you want to copy all the rows from these worksheets which column C contain the text “Completed” into a new worksheet. How could you solve this problem quickly and easily without copying and pasting them one by one manually?
The following VBA code can help you to copy specific rows from all worksheets within the workbook based on a certain condition into a new worksheet. Please do as this:
1. Hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window.
2. Click Insert > Module, and paste the following code in the Module Window.
VBA code: Copy rows from multiple sheets based on criteria into a new sheet
Public Sub CopyRows_ValuesAndNumberFormats() Dim xWs As Worksheet Dim xCWs As Worksheet Dim xRg As Range Dim xStrName As String Dim xRStr As String Dim xRRg As Range Dim xC As Integer On Error Resume Next Application.DisplayAlerts = False xStr = "Kutools for Excel" xRStr = "Completed" Set xCWs = ActiveWorkbook.Worksheets.Item(xStr) If Not xCWs Is Nothing Then xCWs.Delete End If Set xCWs = ActiveWorkbook.Worksheets.Add xCWs.Name = xStr xC = 1 For Each xWs In ActiveWorkbook.Worksheets If xWs.Name <> xStr Then Set xRg = xWs.Range("C:C") Set xRg = Intersect(xRg, xWs.UsedRange) For Each xRRg In xRg If xRRg.Value = xRStr Then xRRg.EntireRow.Copy xCWs.Cells(xC, 1).PasteSpecial xlPasteValuesAndNumberFormats xC = xC + 1 End If Next xRRg End If Next xWs Application.DisplayAlerts = True End Sub
Note: In the above code:
- The text “Completed” in this xRStr = "Completed" script indicates the specific condition that you want to copy rows based on;
- C:C in this Set xRg = xWs.Range("C:C") script indicates the specific column where the condition locates.
3. Then, press F5 key to run this code, and all the rows with the specific condition have been copied and pasted into a new worksheet named Kutools for Excel within the current workbook. See screenshot:
More relative pull or copy data articles:
- Copy Data To Another Worksheet With Advanced Filter In Excel
- Normally, we can quickly apply the Advanced Filter feature to extract data from the raw data in the same worksheet. But, sometimes, when you try to copy the filtered result to another worksheet, you will get the following warning message. In this case, how could you deal with this task in Excel?
- Copy Rows To New Sheet Based On Column Criteria In Excel
- For example, there is a fruit purchase table, and now you need to copy records to new sheet based on specified fruit, how to get it done easily in Excel? Here I will introduce a couple of methods to copy rows to new sheet based on column criteria in Excel.
- Copy Rows If Column Contains Specific Text/Value In Excel
- Supposing you want to find out cells containing specific text or value in a column, and then copy the entire row where the found cell is in, how could you deal with it? Here I will introduce a couple of methods to find if column contains specific text or value and then copy the entire row in Excel.
You are guest
or post as a guest, but your post won't be published automatically.
To post as a guest, your comment is unpublished.· 4 months agoHello there, thank you so much for the code above, it solved me a problem with a complex file; a solution I have been looking for a while now. Thank you..I have one question. How do I change the code so that it copies the rows but only from colum A to colum Q, so not Entire.Row?Thank you in advance and great work!