Paste Special macro Excel-CodePudding

Sub Copy_Cell2()

'Declare Variables
    Dim Wr As Worksheet

'Define the excel sheet

    Set Wr = ThisWorkbook.Sheets("Sheet2")

'Code to stop screen updating and flickering ON

    Application.ScreenUpdating = False

'Select Sheet1 to get the random value

    Sheets("Sheet1").Select
    Cells.Select
    Range("F1").Select
    Range("F1").Copy

'Selects Sheet2 to paste the random value in the next blank cell in column A

    Sheets("Sheet2").Select

    Range("A2").Select

    nrlife = Wr.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Select

'Establish what row has been selected

    With ActiveCell

    vRow = .Row

    End With

    vRange = "A" & vRow & ""

    Range(vRange).Select

'Code to paste value and format from Sheet1 to first empty row on sheet2
    
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks_:=False, Transpose:=False

'Code to stop screen updating and flickering OFF

    Application.ScreenUpdating = True

End Sub

I am getting error in Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks_:=False, Transpose:=False

Error code is Run-time error '1004' Aplication-defined or object-defined error

Can anyone help me what am i missing?

CodePudding user response：

Your code reduces to:

Dim wb As Workbook

Set wb = ThisWorkbook

wb.Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Offset(1).Value = _
                                  wb.Sheets("Sheet1").Range("F1").Value

You can assign values directly - no need for copy/paste in this case.