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

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

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

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

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

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

Dave Peterson Wrote:
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


--
pianoman
------------------------------------------------------------------------
pianoman's Profile: http://www.excelforum.com/member.php...o&userid=33712
View this thread: http://www.excelforum.com/showthread...hreadid=545061