Do Until?

[Pam]

I'm fairly new at this..
I want to list all worksheets in my workbook until one named x.
I'm using this code:
Sub listworksheets()
x = 6
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
Cells(x, 1).Value = ws.Name
x = x + 1
Next ws

How do I change code to do what I want.

Thank you
End Sub

 

[Joel]

x = 6
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
if ws.name <> "X" then
Cells(x, 1).Value = ws.Name
x = x + 1
else
exit for
Next ws

 

[JP]

How about

x = 6
Dim ws As Worksheet

Do Until ws.Name = "x"
Cells(x, 1).Value = ws.Name
x = x + 1
Loop


HTH,
JP

 

[Mike H]

Pam,

If i've understood correctly, try this

Sub listworksheets()
x = 6
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
If ws.Name = "Whatever" Then Exit Sub
Cells(x, 1).Value = ws.Name
x = x + 1
Next ws
End Sub

Mike

 

[Pam]

Thanks for the quick reply; however, it isn't recognizing the "For" and gives
me a compile error.

 

[Joel]

I forgot the "End IF"

x = 6
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
If ws.Name <> "X" Then
Cells(x, 1).Value = ws.Name
x = x + 1
Else
Exit For
End If
Next ws

 

[Rick Rothstein \(MVP - VB\)]

You could have your macro call this subroutine passing in the name of the
sheet you want to stop at...

Sub ListSheetsUpTo(SheetName As String)
Dim X As Long
For X = 1 To Worksheets.Count
If UCase$(Worksheets(X).Name) <> UCase$(SheetName) Then
Cells(X, 1).Value = Worksheets(X).Name
Else
Exit Sub
End If
Next
End Sub

You would call this subroutine from your own macro like this...

ListSheetsUpTo "SheetX"

Rick

 

[pam]

I just added the "end if". Thank you all so much!