vba Range().Select

Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: vba Range().Select

  1. #1
    Board Regular
    Join Date
    Jun 2014
    Location
    Milky Way Galaxy, Earth, US, LI, NY
    Posts
    391
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Question vba Range().Select

     
    Hello Everyone,

    How would you create a macro for the curser to go to
    Code:
     Range(A1).Select
    for every ODD sheet?

    Likewise, how would you create a macro for the curser to go to
    Code:
     Range(B1).Select
    for every EVEN sheet?

    Many thanks in advance!
    Pinaceous

  2. #2
    Board Regular Kenneth Hobson's Avatar
    Join Date
    Feb 2007
    Location
    Tecumseh, OK
    Posts
    2,712
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: vba Range().Select

    In ThisWorkbook object:
    Code:
    Private Sub Workbook_SheetActivate(ByVal Sh As Object)
      If Sh.Index Mod 2 = 0 Then
        Sh.[B1].Activate
        Else
        Sh.[a1].Activate
      End If
    End Sub
    Select can be used as well.
    Last edited by Kenneth Hobson; Nov 13th, 2017 at 10:18 PM.

  3. #3
    Board Regular
    Join Date
    Jul 2007
    Location
    Sydney
    Posts
    3,812
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: vba Range().Select

    Hi Pinaceous,

    Seems like a lot of code, but this works:

    Code:
    Option Explicit
    Sub Macro1()
    
        Dim wsMySheet As Worksheet
        
        Application.ScreenUpdating = False
        
        For Each wsMySheet In ThisWorkbook.Sheets
            On Error Resume Next 'Account for non-numeric tabs
                If Evaluate("MOD(" & wsMySheet.Name & ",2)") = 1 Then 'Sheet tab is odd
                    If Err.Number = 0 Then
                        With wsMySheet
                            .Select
                            .Range("A1").Select
                        End With
                    Else 'Sheet tab is even
                        With wsMySheet
                            .Select
                            .Range("B1").Select
                        End With
                    End If
                End If
            On Error GoTo 0
        Next wsMySheet
        
        Application.ScreenUpdating = True
        
    End Sub
    HTH

    Robert

  4. #4
    Board Regular
    Join Date
    Jun 2014
    Location
    Milky Way Galaxy, Earth, US, LI, NY
    Posts
    391
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: vba Range().Select

    Quote Originally Posted by Kenneth Hobson View Post
    In ThisWorkbook object:
    Code:
    Private Sub Workbook_SheetActivate(ByVal Sh As Object)
      If Sh.Index Mod 2 = 0 Then
        Sh.[B1].Activate
        Else
        Sh.[a1].Activate
      End If
    End Sub
    Select can be used as well.
    Hi Kenneth,

    I do like the simplicity of your approach.

    Is this sub supposed to automatically designate the curser to those ranges for that odd/even sheet?

    Thanks,

    Pinaceous
    Last edited by Pinaceous; Nov 14th, 2017 at 06:18 PM.

  5. #5
    Board Regular Kenneth Hobson's Avatar
    Join Date
    Feb 2007
    Location
    Tecumseh, OK
    Posts
    2,712
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: vba Range().Select

    It makes the activecell that one. Think of it as if you had moved to the cell by tab, arrow keys, clicked with mouse, etc. It is probably closer to Goto (F5).

    It takes less than a minute to add and test.
    Last edited by Kenneth Hobson; Nov 14th, 2017 at 06:38 PM.

  6. #6
    Board Regular
    Join Date
    Jun 2014
    Location
    Milky Way Galaxy, Earth, US, LI, NY
    Posts
    391
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: vba Range().Select

    Hello Kenneth,

    In researching our forum posts on this topic I've came across this thread:

    https://www.mrexcel.com/forum/excel-...file-open.html


    Where I've found this code:


    Code:
    Private Sub Workbook_Open()
     Dim WSheet As Worksheet
    
        For Each WSheet In Worksheets
        WSheet.Activate
        Range("A1").Select
        Next
     End Sub


    It works very nicely for my application here.

    But it does not do completely what I am requesting:


    Quote Originally Posted by Pinaceous View Post

    How would you create a macro for the curser to go to
    Code:
     Range(A1).Select
    for every ODD sheet?

    Likewise, how would you create a macro for the curser to go to
    Code:
     Range(B1).Select
    for every EVEN sheet?


    I've reached out to this thread but it seems it is abandoned due to its age.


    Would you be willing to work with this code in producing my above request?


    Many thanks,
    Pinaceous

  7. #7
    Board Regular
    Join Date
    Jun 2014
    Location
    Milky Way Galaxy, Earth, US, LI, NY
    Posts
    391
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: vba Range().Select

    Quote Originally Posted by Pinaceous View Post
    Hello Kenneth,

    In researching our forum posts on this topic I've came across this thread:

    https://www.mrexcel.com/forum/excel-...file-open.html


    Where I've found this code:


    Code:
    Private Sub Workbook_Open()
     Dim WSheet As Worksheet
    
        For Each WSheet In Worksheets
        WSheet.Activate
        Range("A1").Select
        Next
     End Sub


    It works very nicely for my application here.

    But it does not do completely what I am requesting:






    I've reached out to this thread but it seems it is abandoned due to its age.


    Would you be willing to work with this code in producing my above request?


    Many thanks,
    Pinaceous

    Hello Kenneth,

    Please disregard my last post #6 .


    In working with your code:

    Code:
     Private Sub Workbook_SheetActivate(ByVal Sh As Object)
      If Sh.Index Mod 2 = 0 Then
        Sh.[B1].Activate
        Else
        Sh.[a1].Activate
      End If
    End Sub
    Would you know how to omit a sheet from this code?

    For example, if the sheet is named "TOTALS" it will omit it from the code and do
    Code:
     Sh.[c1].Activate
    ??

    Thank you,
    Pinaceous
    Last edited by Pinaceous; Nov 14th, 2017 at 11:00 PM.

  8. #8
    Board Regular
    Join Date
    Jul 2007
    Location
    Sydney
    Posts
    3,812
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: vba Range().Select

    Try this:

    Code:
    Option Explicit
    Private Sub Workbook_SheetActivate(ByVal Sh As Object)
        If Sh.Name = "TOTALS" Then
            Sh.[C1].Activate
        ElseIf Sh.Index Mod 2 = 0 Then
            Sh.[B1].Activate
        Else
            Sh.[a1].Activate
        End If
    End Sub
    Robert

  9. #9
    Board Regular Kenneth Hobson's Avatar
    Join Date
    Feb 2007
    Location
    Tecumseh, OK
    Posts
    2,712
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: vba Range().Select

    Robert's example will work fine.

    If you have a lot of exceptions, you might want to go with Select Case. When the sheet Name (tab Name) could change, Codename is the better route. In the VBA Project Explorer, you can change Codenames just as easily as Sheet names in the Properties. If Project Explorer and Properties windows are not open, select them in Visual Basic Editor's (VBE's) View menu.

    Be sure to set the order of preference in Cases as the 1st Case=True will execute and then skip to the end.

    e.g.
    Code:
    Private Sub Workbook_SheetActivate(ByVal Sh As Object)
      With Sh
        Select Case True
          Case .CodeName = "Sheet3": .[C1].Activate
          Case .CodeName = "Ken": .[K1].Activate
          Case .Index Mod 2 = 0: .[B1].Activate
          Case Else: .[A1].Activate
        End Select
      End With
    End Sub
    When using codenames, try changing them to something more descriptive than Sheet1. In this case, the tabname TOTALS is my codename Sheet3. I could have just as easily set both to the same names as is the default for new sheets.
    Last edited by Kenneth Hobson; Nov 14th, 2017 at 11:42 PM.

  10. #10
    Board Regular
    Join Date
    Jun 2014
    Location
    Milky Way Galaxy, Earth, US, LI, NY
    Posts
    391
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: vba Range().Select

      
    Thank you Kenneth & Trebor76,

    I appreciate all of your codes and advice. Thanks for also weeding through my mess up there!

    One more question that I have to ask:

    Can it be possible to have two exceptions??

    Code:
      If Sh.Name = "TOTALS" Then
    ->
    Code:
    Sh.[C1].Activate
    And

    Code:
      If Sh.Name = "FORMAT" Then
    ->
    Code:
    Sh.[B11].Activate

    I did try and reason it out on my end, but nothing is coming up that worked, where I'm getting a lot of errors!


    Thank you!
    Pinaceous
    Last edited by Pinaceous; Nov 15th, 2017 at 12:23 AM.

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com