Worksheet Loop searching for yellow

sscornav

Board Regular
Joined
Mar 20, 2010
Messages
113
What would be the syntax to copy the row with "Values and format"?

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

Some videos you may like

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,204
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
 

Watch MrExcel Video

Forum statistics

Threads
1,101,819
Messages
5,483,090
Members
407,379
Latest member
Bender1964

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top