Simple Excel VBA – copy pasting to another workbook

Question

I've looked at couple of codes on copy pasting from one excel workbook to another. For some reason, I am getting errors even when I am directly copying the format. My code is only ~12 lines so far, so if you could look and see what the issue is, that would be really great help to me. Thank you very much!

Sub UpdateActualWorkbook()

    Sheets("Input").Select
    Range("BE9").Select
    Selection.Copy

    Dim Display As String
    Display = Cells(1, 2).Value

    If Display = "Yes" Then

    Dim wb As Workbook
    Set wb = Workbooks.Open("Book1")
    wb.Sheets("Sheet1").Range("A1").PasteSpecial

End If
End Sub

Currently I am getting an error message on wb.Sheets("Sheet1").Range("A1").PasteSpecial ^this line. This does not change whether I make it .Paste or .Pastespecial.

I would be really grateful if anyone could help me. Thank you so much!

Answer 1

If:

Set wb = Workbooks.Open("Book1")

fails, then wb will be Nothing and the PasteSpecial line will raise an error. If you simply want to add a new workbook, then use:

Set wb = Workbooks.Add

and if you want to open an existing workbook, then give the full filespec.

Answer 2

I know it late but try this...

Sub tstcpy()
If Cells(1, 2).Value = “yes” Then
Workbooks("Book1a.xlsm").Sheets("Sheet1").Cells(9, 2).Copy _
Destination:=Workbooks("Book2.xlsx").Sheets("Sheet1").Cells(1, 1)
End If

End Sub