Range("A1").select doesn't work!

[pianoman]

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

 

[Dave Peterson]

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]

Hi Dave,
Works perfectly. A very small adjustment, and it dropped straight in.
Thank you very very much.

Much appreciated. I learnt something too!

Cheers,

Gareth