vba Range().Select

[Pinaceous]

Hello Everyone,

How would you create a macro for the curser to go to

 Range(A1).Select

for every ODD sheet?

Likewise, how would you create a macro for the curser to go to

 Range(B1).Select

for every EVEN sheet?

Many thanks in advance!
Pinaceous

 

[Kenneth Hobson]

In ThisWorkbook object:

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.

 

[Trebor76]

Hi Pinaceous,

Seems like a lot of code, but this works:

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

 

[Pinaceous]

In ThisWorkbook object:

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

 

[Kenneth Hobson]

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.

 

[Pinaceous]

Hello Kenneth,

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

https://www.mrexcel.com/forum/excel...tomatically-set-cursor-cell-a1-file-open.html


Where I've found this 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:

How would you create a macro for the curser to go to

 Range(A1).Select

for every ODD sheet?

Likewise, how would you create a macro for the curser to go to

 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

 

[Pinaceous]

Hello Kenneth,

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

https://www.mrexcel.com/forum/excel...tomatically-set-cursor-cell-a1-file-open.html


Where I've found this 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:

 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

 Sh.[c1].Activate

??

Thank you,
Pinaceous

 

[Trebor76]

Try this:

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

 

[Kenneth Hobson]

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.

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.

 

[Pinaceous]

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??

  If Sh.Name = "TOTALS" Then

->

Sh.[C1].Activate

And

  If Sh.Name = "FORMAT" Then

->

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