0

New to VBA

I am copying from an open workbook over to a closed workbook. It will paste values and formatting, but if I add paste special at the end of the range, I get "compile error - expected end statement". Help appreciated.

Sub vba_copy_Value_to_Inactive_Range()
Dim mybook As Workbook

Application.ScreenUpdating = False

Set wb = Workbooks.Open("E:\test file.xlsx")

ThisWorkbook.Sheets("Request").Range("E2").Copy Destination:=wb.Sheets("Request").Range("E4").PasteSpecial Paste:=xlPasteValues

mybook.Close SaveChanges:=True
Application.ScreenUpdating = True

End Sub
Perry_M
  • 27
  • 7
  • `Copy` belongs on one line, and `PasteSpecial` on another.... see the linked dupe. Drop the `Destination:=` and move `wb.Sheets("Request").Range("E4").PasteSpecial Paste:=xlPasteValues` to the next line. – BigBen Aug 05 '20 at 16:24
  • I just did that and I still get an error. – Perry_M Aug 05 '20 at 16:37
  • 1
    What error message? Note that you don't need the clipboard. `wb.Sheets("Request").Range("E4").Value = ThisWorkbook.Sheets("Request").Range("E2").Value`. – BigBen Aug 05 '20 at 16:39
  • Yes! That's all I was needing. Thank You! – Perry_M Aug 05 '20 at 16:54

0 Answers0