Copy and paste row if a cell has a value

Hello,

I am using the following code to copy an entire row when that row has data in column Y, and then paste that row into a different worksheet.

Sub CopyAndPaste()

Call CopyAndPasteSpecial

Application.ScreenUpdating = False
NR = 2
Dim cel As Range
With Sheets("Processor")
     For Each cel In .Range("Y6:Y" & .Cells(.Rows.Count, "Y").End(xlUp).Row).SpecialCells(2)
         cel.EntireRow.Copy
         Sheets("Results").Range("A" & NR).PasteSpecial Paste:=xlPasteValues
         NR = NR + 1
    Next cel
 End With
 Application.CutCopyMode = False
 Application.ScreenUpdating = True
 
End Sub

Because the data in column Y is generated via a formula, the first thing I'm doing is copying and pasting special as values.

Sub CopyAndPasteSpecial()

    Sheets("Processor").Select
    Range("Y6:Y27").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
End Sub

Does anyone know why calling this second macro would cause the first macro to return every row (even those without data in column Y)?

THANK YOU!

Mattman,
The first code pastes VALUES ONLY, so why do you to paste them again?
If you are calling the first macro from the second macro, the first amro has to complete running until the sub ends, unless otherwise, you instructed to stop somewhere.

The macro doesn't seem to work if the cells in column Y are pulling data from other cells (gets an error); therefore, I ran the copy and paste special macro BEFORE running the one you wrote.

But when I run the paste special macro and then the one you wrote, it copies and pastes all of the rows, not just ones with values in column Y.

Try this

Sub CopyAndPaste()

Call CopyAndPasteSpecial

Application.ScreenUpdating = False
NR = 2
Dim cell As Range
With Sheets("Processor")

     For Each cell In .Range("Y6:Y" & .Range("Y" & Rows.Count).End(xlUp).Row) '.SpecialCells(2)
     If cell.Value <> "" Then
         cell.EntireRow.Copy
         Sheets("Results").Range("A" & NR).PasteSpecial Paste:=xlPasteValues
         NR = NR + 1
         End If
    Next cell
 End With
 Application.CutCopyMode = False
 Application.ScreenUpdating = True

End Sub

Sub CopyAndPasteSpecial()

    Sheets("Processor").Select
    Range("Y6:Y27").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
End Sub

Try this

Sub CopyAndPaste()

Call CopyAndPasteSpecial

Application.ScreenUpdating = False
NR = 2
Dim cell As Range
With Sheets("Processor")

     For Each cell In .Range("Y6:Y" & .Range("Y" & Rows.Count).End(xlUp).Row) '.SpecialCells(2)
     If cell.Value <> "" Then
         cell.EntireRow.Copy
         Sheets("Results").Range("A" & NR).PasteSpecial Paste:=xlPasteValues
         NR = NR + 1
         End If
    Next cell
 End With
 Application.CutCopyMode = False
 Application.ScreenUpdating = True

End Sub

Sub CopyAndPasteSpecial()

    Sheets("Processor").Select
    Range("Y6:Y27").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
End Sub

The macro doesn't seem to work if the cells in column Y are pulling data from other cells (gets an error);

Regardless of the value of column Y, my code converts the formulae in to values if that cell is empty.
o you want to copy all cells(Including the blank), and convert them in to values?