Prev: Help with code
Next: Error message : You must select a shape
From: Wes_A on 20 Apr 2010 23:44
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
From: Jacob Skaria on 21 Apr 2010 00:46
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


--
Jacob (MVP - Excel)


"Wes_A" wrote:

> 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
 | 
Pages: 1
Prev: Help with code
Next: Error message : You must select a shape