move sheets until last one

[confused in Frankfurt]

Hi,

I am trying to navigate from one sheet to next in a loop until the last sheet where it should stop the loop. There are 24 sheets in total and I keep getting an error at the last sheet. How to fix?

Do While IsError(Sheets.Count) = False
Sheets(ActiveSheet.Index + 1).Activate
Loop

Regards
Sarah

 

[Misca]

Try:

Dim i As Integer

For i = ActiveSheet.Index + 1 To Sheets.Count
    Sheets(i).Activate
Next i

 

[confused in Frankfurt]

Thanks Misca

this works - in the meantime I changed my loop to stop at last sheet

Do

Sheets(ActiveSheet.Index + 1).Activate


' rest of code to be carried out on each sheet



Loop Until ActiveSheet.Name = "Italy" ' name of last sheet

where would I split your code to enter the rest of coding to be carried out on each sheet before moving to the next?

 

[Misca]

You don't have to activate a sheet to make VBA do something with it and it's usually much faster not to change sheets unless necessary.

If you want to do something with each sheet you can use something like

Dim i As Integer

For i = ActiveSheet.Index To Sheets.Count
    With Sheets(i)
        .Range("A1").Value = .Name  'Writes the sheet name to A1
        .Range("A2:H10").Formula = "=round(rand()*10,0)"    'Writes a formula to A2:H10
        .Range("I2:I10").Formula = "=sum(A2:H2)"    'Writes another formula to I2:I10
    End With
Next i

From the code above you can see where you should put your piece of code. The "Loop Until" -code is not necessary because the code ends at last sheet anyway. Also notice, that the previous code started from next sheet (=ActiveSheet.Index +1) where this piece of code starts from active sheet.