Run Time Error '1004' Application or Onject-defined

[Wes_A]

Hi all.
I am having a strange problem with the below code in that it produces the
run time error when trying to Paste the Selection.
However, if I perform the action manually in exactly the same way, as in
recording the macro in the first place, it works!?
How can I get this to run - it is after all, a really simple code. (Most
frustrating)
Thanks, Wes.


Windows("Transaction_Record.xlsm").Activate
Sheets("DETAILED_PMNTS_REC").Select
Range("N2077:QV2089").Select
Application.CutCopyMode = False
Selection.Copy
Windows("AUDIT_TRAIL.xlsm").Activate
Sheets("AUDIT_TRAIL_MONTHLY").Select
Range("B74").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks_:=False, Transpose:=False

 

[Jacob Skaria]

Try the below....which uses the worksheet object..

Edit the sheet names to suit

Sub Macro()

Dim wsSource As Worksheet, wsTarget As Worksheet

Set wsSource = Workbooks("Transaction_Record.xlsm").Sheets("Sheetname")
Set wsTarget = Workbooks("AUDIT_TRAIL.xlsm").Sheets("Sheetname")

wsSource.Range("N2077:QV2089").Copy
wsTarget.Range("B74").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

End Sub