Page 2 of 2 FirstFirst 12
Results 11 to 13 of 13

Thread: Worksheet Loop searching for yellow

  1. #11
    Board Regular
    Join Date
    Mar 2010
    Posts
    111
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Worksheet Loop searching for yellow

    What would be the syntax to copy the row with "Values and format"?

    currentRow.Copy destinationWorksheet.Cells(rowNumber, "a")

  2. #12
    MrExcel MVP
    Join Date
    Mar 2004
    Location
    Canada
    Posts
    18,855
    Post Thanks / Like
    Mentioned
    35 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Worksheet Loop searching for yellow

    Try the following macro, which has been amended to paste values and formats. Note that ScreenUpdating has been set to False at the beginning of the macro to prevent the screen from updating and improve efficient. And it's set back to True and the end of the macro.

    Code:
    Sub CopyRows()
    
        Dim destinationWorksheet As Worksheet
        Dim currentWorksheet As Worksheet
        Dim currentRow As Range
        Dim currentCell As Range
        Dim rowNumber As Long
        
        Application.ScreenUpdating = False
        
        Set destinationWorksheet = Worksheets("XXX")
        
        destinationWorksheet.Cells.Clear
        
        rowNumber = 2 'start at Row 2
        For Each currentWorksheet In ActiveWorkbook.Worksheets
            If currentWorksheet.Name <> destinationWorksheet.Name Then
                For Each currentRow In currentWorksheet.UsedRange.Rows
                    For Each currentCell In currentRow.Cells
                        If Not IsError(currentCell) Then
                            If InStr(1, currentCell.Value, "XYZ", vbTextCompare) > 0 Then
                                If currentCell.Interior.Color = vbYellow Then
                                    currentRow.Copy
                                    With destinationWorksheet.Cells(rowNumber, "a")
                                        .PasteSpecial xlPasteValues
                                        .PasteSpecial xlPasteFormats
                                    End With
                                    rowNumber = rowNumber + 1
                                    Exit For
                                End If
                            End If
                        End If
                    Next currentCell
                Next currentRow
            End If
        Next currentWorksheet
        
        With destinationWorksheet
            .Activate
            .Cells(1).Select
        End With
        
        Application.ScreenUpdating = True
        
    End Sub

  3. #13
    Board Regular
    Join Date
    Mar 2010
    Posts
    111
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Worksheet Loop searching for yellow

    Thanks so much for your help Domenic!

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •