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 <P...@discussions.microsoft.com> 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" <(E-Mail Removed)> wrote in message
news:A56D282B-5B3A-421C-9813-(E-Mail Removed)...
> 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 <P...@discussions.microsoft.com> 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
>
>