CODEWITHSUNDEEP
vbaloopsexcel
user1115535
user1115535

Reputation: 131

VBA-Loop with some worksheets

I am a beginner and I would like to do a loop in all the worksheets of my excel file, except the first one. However the code below only works on the second one. Could you please explain me what is wrong in this code?

Many thanks

Sub MobileTCalculation()
'MobileTCalculation Macro
Dim i As Integer

For i = 1 To 40
Worksheets(1 + 1).Select
Range("A20").Select
On Error Resume Next
Next i

End Sub

Upvotes: 4

Views: 7547

Answers (2)

Siddharth Rout
Siddharth Rout

Reputation: 149325

If you want to skip the first sheet then change the loop as shown below. Worksheets(i + 1) will give you errors if there are only 40 sheets in your workbook ;)

Use this

Sub MobileTCalculation()
    Dim i As Integer

    For i = 2 To 40
        Worksheets(i).Range("A20").Select
    Next i

End Sub

Also two things.

1) Use of On Error Resume Next is evil ;) Use it only when necessary.

2) Don't use .SELECT It slows down your code. Instead directly perform the action. For example

Sub MobileTCalculation()
    Dim i As Integer

    For i = 2 To 40
        With Worksheets(i).Range("A20")
            Debug.Print .Value
        End With
    Next i

End Sub

HTH

Sid

Upvotes: 3

joshuahealy
joshuahealy

Reputation: 3569

You should change:

Worksheets(1 + 1).Select

so it uses your i variable... you've just put 1 + 1 so it always evaluates to 2

A classic mistake :)

Upvotes: 1

Related Questions