Loop through each tabs in excel and active it one by one in Excel by VBA

Question

I am trying to come up with a solution in VBA, what I want to do is to active each tab in this file and active certain cell for each tab based on different requirement. However my code didn't work, for the statement:

wkbk1.ws.Activate

It said Object doesn't not support this property or method(not sure why, my ws is the worksheet right?)

My code is:

For Each ws In wkbk1.Worksheets
wkbk1.ws.Activate ' error here
If wkbk1.ws.Name = "Page" Then
wkbk1.ws.Range("B2").Select
ElseIf wkbk1.ws.Name = "Contents" Or "Results" Then
wkbk1.ws.Range("B1").Select
ElseIf wkbk1.ws.Name = "Logic" Then
wkbk1.ws.Range("C1").Select
Else
wkbk1.ws.Range("A1").Select
End If
Next

Can anyone please give me an idea for why I am having this issue?

Directly after your `For Each ws In ...` you must use the `ws` directly (within this loop without a leading workbook-reference) to reference each sheet, like `ws.Name` or `ws.Range()`. You should also [avoid selecting or activating anything](https://stackoverflow.com/q/10714251/10908769). — brno, May 09 '19 at 05:28
May I ask what the purpose of "activate each tab one by one" is? Because that doesn't make any sens at all and you will only see the last activation in the end. — Pᴇʜ, May 09 '19 at 06:36
In addition to the comments above, what's the purpose of selecting the cells? What do you want to do with them? Since you know how to refer to them, you can do anything you want without selecting them. Currently you are not doing anything. Also, have you `set` your `wkbk1` object? — Stavros Jon, May 09 '19 at 08:52
@Pᴇʜ thanks for you response, the purpose is I have several tabs in the file, I want to when people open the file, the mouse is not click everywhere, for example, some in A10, some in B30, it is not necessary to be A1, but the arrow shouldn't not be randomly everywhere — Worst SQL Noob, May 10 '19 at 06:26
@StavrosJon thanks for you response, the purpose is I have several tabs in the file, I want to when people open the file, the mouse is not click everywhere, for example, some in A10, some in B30, it is not necessary to be A1, but the arrow shouldn't not be randomly everywhere — Worst SQL Noob, May 10 '19 at 06:27

YasserKhalil · Answer 1 · 2019-05-09T05:16:05.527

0

Don't use Activate (You don't need it)

Instead of using such a line

wkbk1.ws.Range("B2").Select

use Application.GoTo like this

Application.Goto ws.Range("B2")

edited May 09 '19 at 05:16

answered May 09 '19 at 05:11

YasserKhalil

9,138
7
36
95

score 0 · Answer 2 · answered May 10 '19 at 06:35

Just do a Select Case to determine the address to jump to for every sheet. And finally use the Application.Goto method to jump there.

Option Explicit

Public Sub InitializeWorksheetsSelectedCells()
    Dim JumpToAddress As String

    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        Select Case ws.Name
            Case "Page"
                JumpToAddress = "B2"
            Case "Contents", "Results"
                JumpToAddress = "B1"
            Case "Logic"
                JumpToAddress = "C1"
            Case Else
                JumpToAddress = "A1"
        End Select

        Application.Goto ws.Range(JumpToAddress)
    Next ws
End Sub

Loop through each tabs in excel and active it one by one in Excel by VBA

2 Answers2