Run Time Error 9???

[Fable]

I�m working on this piece of code that will use Col. �A� as a link to extract X row (Sheet3) and copy the info to Sheet1. It works fine up to the point of Copy after that it returns a �Run Time Error 9� / Subscript out of Range. Any ideas how to correct?

Any help is appreciated!
====================================================
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True
Dim IntersectRange As Range
Set WatchRange = Range("A10:A5000")
Set IntersectRange = Intersect(Target, WatchRange)
On Error GoTo 0
Application.EnableEvents = False

With ActiveWorkbook.Worksheets("Sheet3")
If IntersectRange Is Nothing Then
MsgBox ("NO SELECTION MADE")
Else
Target = "B" & ActiveCell.Row & ":V" & ActiveCell.Row
End If
End With
Range(Target).Select
Selection.Copy
Sheets("Sheet1").Range ("AP12")
ActiveSheet.Paste
Application.CutCopyMode = False
Application.EnableEvents = True
End Sub

[Toppers]

Hi,

Try this:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
Cancel = True
Dim IntersectRange As Range
Set WatchRange = Range("A10:A5000")
Set IntersectRange = Intersect(Target, WatchRange)
On Error GoTo 0
Application.EnableEvents = False

With ActiveWorkbook.Worksheets("Sheet3")
If IntersectRange Is Nothing Then
MsgBox ("NO SELECTION MADE")
Else
.Range("B" & ActiveCell.Row & ":V" & ActiveCell.Row).Copy
Sheets("Sheet1").Range("AP12")
End If
End With
Application.CutCopyMode = False
Application.EnableEvents = True
End Sub


"Fable" wrote:

>
> I’m working on this piece of code that will use Col. “A” as a link to
> extract X row (Sheet3) and copy the info to Sheet1. It works fine up to
> the point of Copy after that it returns a “Run Time Error 9” / Subscript
> out of Range. Any ideas how to correct?
>
> Any help is appreciated!
> ====================================================
> Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel
> As Boolean)
> Cancel = True
> Dim IntersectRange As Range
> Set WatchRange = Range("A10:A5000")
> Set IntersectRange = Intersect(Target, WatchRange)
> On Error GoTo 0
> Application.EnableEvents = False
>
> With ActiveWorkbook.Worksheets("Sheet3")
> If IntersectRange Is Nothing Then
> MsgBox ("NO SELECTION MADE")
> Else
> Target = "B" & ActiveCell.Row & ":V" & ActiveCell.Row
> End If
> End With
> Range(Target).Select
> Selection.Copy
> Sheets("Sheet1").Range ("AP12")
> ActiveSheet.Paste
> Application.CutCopyMode = False
> Application.EnableEvents = True
> End Sub
>
>
> --
> Fable
>
>
> ------------------------------------------------------------------------
> Fable's Profile: http://www.excelforum.com/member.php...fo&userid=2185
> View this thread: http://www.excelforum.com/showthread...hreadid=501470
>
>

[Fable]

Thanks Toppers! it still shows a run time error, however I worked out a solution, basically paste the "result" on the same Sheet3 then copy / export to sheet1 it an extra steps but it works.

[Toppers]

FYI: it worked OK when I tested it.

The line Sheets("Sheet1").Range("AP12") should be on the same as the COPY
line NOT a seperate line s shown in the posting: is this the problem?

"Toppers" wrote:

> Hi,
>
> Try this:
>
> Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
> Boolean)
> Cancel = True
> Dim IntersectRange As Range
> Set WatchRange = Range("A10:A5000")
> Set IntersectRange = Intersect(Target, WatchRange)
> On Error GoTo 0
> Application.EnableEvents = False
>
> With ActiveWorkbook.Worksheets("Sheet3")
> If IntersectRange Is Nothing Then
> MsgBox ("NO SELECTION MADE")
> Else
> .Range("B" & ActiveCell.Row & ":V" & ActiveCell.Row).Copy
> Sheets("Sheet1").Range("AP12")
> End If
> End With
> Application.CutCopyMode = False
> Application.EnableEvents = True
> End Sub
>
>
> "Fable" wrote:
>
> >
> > I’m working on this piece of code that will use Col. “A” as a link to
> > extract X row (Sheet3) and copy the info to Sheet1. It works fine up to
> > the point of Copy after that it returns a “Run Time Error 9” / Subscript
> > out of Range. Any ideas how to correct?
> >
> > Any help is appreciated!
> > ====================================================
> > Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel
> > As Boolean)
> > Cancel = True
> > Dim IntersectRange As Range
> > Set WatchRange = Range("A10:A5000")
> > Set IntersectRange = Intersect(Target, WatchRange)
> > On Error GoTo 0
> > Application.EnableEvents = False
> >
> > With ActiveWorkbook.Worksheets("Sheet3")
> > If IntersectRange Is Nothing Then
> > MsgBox ("NO SELECTION MADE")
> > Else
> > Target = "B" & ActiveCell.Row & ":V" & ActiveCell.Row
> > End If
> > End With
> > Range(Target).Select
> > Selection.Copy
> > Sheets("Sheet1").Range ("AP12")
> > ActiveSheet.Paste
> > Application.CutCopyMode = False
> > Application.EnableEvents = True
> > End Sub
> >
> >
> > --
> > Fable
> >
> >
> > ------------------------------------------------------------------------
> > Fable's Profile: http://www.excelforum.com/member.php...fo&userid=2185
> > View this thread: http://www.excelforum.com/showthread...hreadid=501470
> >
> >