VBA - paste as values

Question

I'm struggling with code, which should copy the cell and paste it to the below's row (it the below's row is empty / if not it searches for other rows)

 Sub CopyRows2()

Dim LastRow As Long

With Worksheets("OUTPUT") ' <-- here should be the Sheet's name
    LastRow = .Cells(.Rows.Count, "O").End(xlUp).Row ' last row in column B

For i = 2 To LastRow
If Range("O" & i) > "" And Range("O" & i + 1) = "" Then
    Range("O" & i).Copy
    Range("O" & i + 1).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
Else
    End If
Next

End With

ActiveWindow.ScrollRow = 1 'scrolling the screen to the top

End Sub

This way, it works fine, but in the cells above are formulas and therefore I would need to copy only values, since the results should be same. I've found, that I should replace this part:

ActiveSheet.Paste

by this part:

ActiveSheet.PasteSpecial xlPasteValues

But once I did it, it has showed me an error msg Run-time error 1004 and the macro has stucked after copy 1st cell, then find the empty cell... but it has not inserted the value and has not continue in searching.

Could I ask you for a help, please? What am I missing there? I cannnot see the mistake.

Thank you!

Answer 1

Works for me:

Range("O" & i).Copy
Range("O" & i + 1).PasteSpecial xlPasteValues
Application.CutCopyMode = False

No need to Select here.