+ Reply to Thread
Results 1 to 3 of 3

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

  1. #1
    Registered User
    Join Date
    04-21-2006
    Posts
    61

    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
    Last edited by pianoman; 05-24-2006 at 09:54 AM.

  2. #2
    Dave Peterson
    Guest

    Re: 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

  3. #3
    Registered User
    Join Date
    04-21-2006
    Posts
    61

    It works!

    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

    Quote Originally Posted by 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 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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1