Paste Value Syntax in VBA

[sscornav]

Struggling with this one...

currentRow is a Range
destinationWorksheet is Worksheet

How do I copy with values and format by modifying this statement:

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

New to VBA and struggling with Syntax....thanks!

 

[Rick Rothstein]

Struggling with this one...

currentRow is a Range
destinationWorksheet is Worksheet

Show us the statements that set the currentRow and destinationWorksheet variables.

 

[sscornav]

Option Explicit

Sub CopyRows()


Dim destinationWorksheet As Worksheet
Dim currentWorksheet As Worksheet
Dim currentRow As Range
Dim currentCell As Range
Dim rowNumber As Long

Set destinationWorksheet = Worksheets("XXX")

destinationWorksheet.Cells.Clear

rowNumber = 2 'start to paste 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 destinationWorksheet.Cells(rowNumber, "a")
rowNumber = rowNumber + 1
Exit For
End If
End If
End If
Next currentCell
Next currentRow
End If
Next currentWorksheet

destinationWorksheet.Activate

End Sub

 

[sscornav]

Just looking for the syntax for the Copy statement so I can paste values and format.