In a general module, an unqualified range refers to the activesheet.
But behind a worksheet module, that unqualified range refers to the sheet that
holds the code--and that's not always the activesheet.
You can do lots of stuff without selecting the cells.
I'm not sure what worksheets are what, but something like this may get you
closer:
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim DestCell As Range
If Target.Cells.Count > 1 Then Exit Sub 'one cell at a time??
If Not Intersect(Target, Me.Range("R2:R130")) Is Nothing Then
With Worksheets("Yearly Snapshots")
If IsEmpty(.Range("A2").Value) = True Then
Set DestCell = .Range("a2")
Else
Set DestCell = .Range("a2").End(xlDown).Offset(1, 0)
End If
End With
Target.EntireRow.Copy _
Destination:=DestCell
Sheets("Master Sheet").Activate
Application.Run "dataform2.xla!ShowDataForm"
End If
End Sub
If "Master Sheet" is the sheet with the code, then you don't need that .activate
line near the end. Since we didn't select anything, we're still on that sheet.
This is untested, but it did compile.
pianoman wrote:
>
> This has got me stumped... the most basic of VB commands isn't working?!
> What am I doing wrong???
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> If Not Intersect(Target, Range("R2:R130")) Is Nothing Then
> Range(Target.Address).Select
> ActiveCell.EntireRow.Copy
> Sheets("Yearly Snapshots").Activate
> Range("A1").Select
> If IsEmpty(Range("A2")) = True Then
> Range("A2").Select
> Else
> Selection.End(xlDown).Offset(1, 0).Select
> End If
> ActiveSheet.Paste
> MsgBox "Now please Enter a new Annual Review Date"
> Sheets("Master Sheet").Activate
> Application.Run "dataform2.xla!ShowDataForm"
> End If
> End Sub
>
> Thanks Guys,
>
> Gareth
>
> --
> pianoman
> ------------------------------------------------------------------------
> pianoman's Profile: http://www.excelforum.com/member.php...o&userid=33712
> View this thread: http://www.excelforum.com/showthread...hreadid=545061
--
Dave Peterson
Bookmarks