Skip to main content

How to copy rows from multiple worksheets based on criteria into a new sheet?

Author: Xiaoyang Last Modified: 2019-09-10

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?

Copy rows from multiple worksheets based on criteria into a new sheet with VBA code

Copy rows from multiple worksheets based on criteria into a new sheet with VBA code

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
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
               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.

  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns and Keeping Data; Split Cells Content; Combine Duplicate Rows and Sum/Average... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Favorite and Quickly Insert Formulas, Ranges, Charts and Pictures; Encrypt Cells with password; Create Mailing List and send emails...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • Pivot Table Grouping by week number, day of week and more... Show Unlocked, Locked Cells by different colors; Highlight Cells That Have Formula/Name...
kte tab 201905
  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
officetab bottom
Comments (2)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site

thank you very much for the code. I have a question: the code runs smoothly on some of my sheets, but looks like enters an infinite loop in some other ones which makes excel crash. What could the reason be?
This comment was minimized by the moderator on the site
Hello 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!
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
Rate this post:
0   Characters
Suggested Locations