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

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



"Pam" wrote:

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

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


On Mar 5, 2:26*pm, Pam wrote:
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

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" wrote:

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

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

"Joel" wrote:

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



"Pam" wrote:

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

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

"Pam" wrote:

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

"Joel" wrote:

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



"Pam" wrote:

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

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" wrote in message
...
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

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

"JP" wrote:

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


On Mar 5, 2:26 pm, Pam wrote:
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