Solved: Need help with a simple loop.

[Cartwheels]

I need some help with what would seem like a simple simple loop. What I would like to do is start on sheet 3 do some stuff, go to next sheet, do same stuff, go to next sheet... ect till there are no more. I cannot seem to end it correctly.
Here is what I have:

Sheets("Sheet3").Select
Do
ActiveSheet.Next.Select
'do some stuff
Loop Until ??????
End Sub

Btw, is there a reference sheet on the web that can help with what is ok after loop until?

[nst1107]

The Excel help files do a good job of explaining Do...Loop statements. If you're using 2007, make sure you are searching Developer Reference. Does ActiveSheet.Next.Select work?

[MaximS]

you can try to put something like that into your Sub:

[VBA]
Dim i as Integer

For i = 1 to 10
Sheets(i).Select
'Do some stuff
Next i
[/VBA]

[Bob Phillips]

[vba]

For i = 3 To Worksheets.Count

With Worksheets(i)

'do some stuff
End With
Next i
[/vba]

[Cartwheels]

Thanks for the replies. I still am having some trouble though. I a little bit of a newb, so go easy on me.

Yes, this part does work, ActiveSheet.Next.Select. it is just the until part I end up with an error on.

Let me explain a little more to see if that helps.

I have at least three sheets but don't know how many all together, it will vary.
To keep things simple. lets say I want to clear (A1) starting on sheet 3
and repeating through all the sheets that follow. Till the end.

Thanks again

[Bob Phillips]

Don't do it that way, we have shown you better ways.

[CreganTur]

If you want to understand the basics of loops you can take a look at the article I wrote- just click on the Articles link under my name.

[Cartwheels]

[vba]

For i = 3 To Worksheets.Count

With Worksheets(i)

'do some stuff
End With
Next i
[/vba]

Ahhhhhhh, I finally got it. I kept running into a bug and missed the fact I was missing .select on line 2.
In other words it should be

For i = 3 To Worksheets.Count

With Worksheets(i).select

'do some stuff
End With
Next i


Btw, I love this forum, I have a feeling I'm going to learn a lot here!

[Bob Phillips]

No it shouldn't! Selecting is inefficient. Work via the referenced worksheet object
, not a selected worksheet.

[mdmackillop]

i.e.
[VBA]For i = 3 To Worksheets.Count
With Worksheets(i)
.Range("A1").ClearContents
End With
Next i [/VBA]

[Cartwheels]

Edit: I posted just after you md. I bet that is what I'm missing.

[Cartwheels]

That is it! Thank you everyone!

[Cartwheels]

I know I marked this as solved , but I wanted to see if you guys could help me with one more little thing I need to finish this macro up.

If I wanted to have cell (a5) selected on these sheets when finished what would I need to write in?

For i = 3 To Worksheets.Count
With Worksheets(i)
.Range("A1").ClearContents
Activate (A5) Here on these sheets
End With
Next i

I'm having trouble learning this type of code! Is this VBScript and where can I go to learn this?

[lucas]

You have to reference the range just as you did with A1.

[VBA].range("A5").select[/VBA]

activate is the wrong action.....

Also as Maxim shows in post #3 you should define the variabe i

If you used Option explicit at the top of your module as you should always do, you would have recieved an error if you did not do this....

[lucas]

Actually select will not work either.....what are you doing with A5?

[Cartwheels]

I want to exit the macro with those cells activated if possible. In other words that would be the finishing touch.

[lucas]

I believe the only way you can do that is to select each sheet which is not the most efficient way to go.
[VBA]
Option Explicit
Sub a()
Dim i As Long
For i = 3 To Worksheets.Count
Sheets(i).Select
With ActiveSheet
.Range("A1").ClearContents
.Range("A5").Select
End With
Next i
End Sub

[/VBA]

Unless it is absolutely necessary I would avoid this step and stick with the code provided above.

[mdmackillop]

If it is appropriate, you can set Excel to go to a specific range when a sheet is activated. This code goes into the WorkBook module.
[VBA]Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Select Case Sh.Name
Case "Sheet1"
Range("A5").Select

Case "Sheet2"
Range("B5").Select

End Select
End Sub
[/VBA]