CODEWITHSUNDEEP
excelvba
Malte Susen
Malte Susen

Reputation: 845

VBA: Running Code on Selected Excel Tab and Consecutively on All Tabs to the Right

I have set up a VBA macro that is running a VBA operation consecutively in each spreadsheet of a given Excel file. Given that there are numerous sheets in the file, which leads to occasional interruptions with the server, I have to restart the request every once in a while.

So here comes the question: I am looking to modify the code element below in a way that the VBA macro starts running on the currently selected sheet/tab. It shall then consecutively cover all remaining sheets/tabs to the right of the active sheet, but not those to the left which have already been populated.

The code below restarts the each run of the macro with the very first sheet/tab of the file, which is not necessary. Is there any smart tweak to the code?

  Dim xsheet As Worksheet
  For Each xsheet In ThisWorkbook.Worksheets
  xsheet.Select

Upvotes: 2

Views: 1508

Answers (3)

JohnyL
JohnyL

Reputation: 7152

There are three sheet-specific collections: Worksheets, Charts and DialogSheets. The Index property of these collections returns Sheets's collection index - not the actual index in sheet-specific collection.

Say, you have four sheets:

  1. Worksheet ("Sheet1")
  2. Worksheet ("Sheet2")
  3. Chart ("Chart1")
  4. Worksheet ("Sheet3").

In this case Worksheets("Sheet3").Index returns 4 when the real index is 3. The bottom line is never rely on Index property of sheet-specific collection when it comes to processing all the sheets.

To solve you problem you just need to use Sheets collection:

Sub FFF()
    Dim x%, sheet As Variant
    For x = ActiveSheet.Index + 1 To Sheets.Count
        Set sheet = Sheets(x)
        '// Do something with sheet
    Next
End Sub

Upvotes: 2

VBasic2008
VBasic2008

Reputation: 54838

Continue Through Worksheets

If you want to continue with the ActiveSheet, just remove ' + 1'.

Warning: These codes are valid if you only have worksheets in the workbook, and not charts, dialogs or whatever.
I almost always use only worksheets in my workbooks so I never learned about the Index issue that JohnyL is referring to in his answer to this question.

The Idea

Sub ContinueThroughWorksheets()
    Dim i As Long
    With ThisWorkbook
        For i = .ActiveSheet.Index + 1 To .Worksheets.Count
            Debug.Print .Worksheets(i).Name
        Next
    End With
End Sub

The Implementation

Sub ContinueThroughWorksheets2()
    Dim i As Long
    Dim j As Long
    With ThisWorkbook
       Select Case .ActiveSheet.Index
           Case 1
               j = 1
           Case .Worksheets.Count
               Exit Sub
           Case Else
               j = .ActiveSheet.Index + 1
       End Select
       For i = j To .Worksheets.Count
          Debug.Print .Worksheets(i).Name
       Next
    End With
End Sub

Upvotes: 1

SJR
SJR

Reputation: 23081

You can do it thus, but note that it's generally not advisable to base code on the active sheet or active cells as they can easily be changed and your code may not run correctly.

A better method would be to store the (code)names of the sheets processed elsewhere and loop through your sheets excluding those names.

Sub x()

Dim i As Long

For i = ActiveSheet.Index To Worksheets.Count
    MsgBox Worksheets(i).Name
Next i

End Sub

Upvotes: 1

Related Questions