How to incorporate Loop feature

[Jim May]

The below code works perfectly for my workbook containing 103 worksheets;
At present it lists and numbers all my worksheets down Col A and B only.
I'd like to tweek it to output to only rows 1 - 20, meaning after the first
20 A1:B20 being filled -- transfer output to Column C, then after 20 down
(C120) to E:F and so on..
Loops at this point totally confuse me,, can some one assist?
TIA,

Sub ListWSNames()
Dim ws As Integer
Worksheets("Index").Activate
Cells.ClearContents
Range("B1").Select
For ws = 2 To Worksheets.Count
Worksheets("Index").Cells(ws - 1, 2) = _
Worksheets(ws).Name
ActiveCell.Offset(ws - 2, -1) = ws - 1
Next
Range("B1").Select
End Sub

 

[Bob Phillips]

Sub ListWSNames()
Dim ws As Integer
Dim iCol As Long, iRow As Long

Worksheets("Index").Activate
Cells.ClearContents
iCol = 2: iRow = 1
For ws = 2 To Worksheets.Count
Worksheets("Index").Cells(iRow, iCol) = _
Worksheets(ws).Name
iRow = iRow + 1
If iRow > 20 Then
iCol = iCol + 1
iRow = 1
End If
Next
Range("B1").Select
End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Jim Cone]

Hi Bob,

Shouldn't the line: "If iRow > 20 Then" actually read "If iRow Mod 21 = 0 Then"

Regards,
Jim Cone
San Francisco, USA

 

[Bob Phillips]

Jim,

You are right. Thanks

Bob

Hi Bob,

Shouldn't the line: "If iRow > 20 Then" actually read "If iRow Mod 21 = 0 Then"

Regards,
Jim Cone
San Francisco, USA

 

[Jim May]

Bob:
Thanks for the Code. I ended up modifying to include a sequence column
in order to count the diplayed sheetnames. <<Below>>
Thanks Again,
Jim May

Sub ListWSNames()
Dim ws As Integer
Dim iCol As Long, iRow As Long
Range("B1").Select
Worksheets("Index").Activate
Cells.ClearContents
iCol = 2: iRow = 1
For ws = 2 To Worksheets.Count
Worksheets("Index").Cells(iRow, iCol) = _
Worksheets(ws).Name
ActiveCell.Offset(iRow - 1, -1) = ws - 1
iRow = iRow + 1
If iRow Mod 21 = 0 Then
iCol = iCol + 2
Cells(1, iCol).Select
iRow = 1
End If
Next
Range("B1").Select
End Sub

 

[Bob Phillips]

Hi Jim,

I just answered your original post without twigging that is was you .

You could just add a count at the end, rather than each sheet

Sub ListWSNames()
Dim ws As Integer
Dim iCol As Long, iRow As Long
Dim oWs As Worksheet
Range("B1").Select
Worksheets("Index").Activate
Cells.ClearContents
iCol = 2: iRow = 1
For ws = 2 To Worksheets.Count
Worksheets("Index").Cells(iRow, iCol) = _
Worksheets(ws).Name
iRow = iRow + 1
If iRow Mod 21 = 0 Then
iCol = iCol + 1
Cells(1, iCol).Select
iRow = 1
End If
Next
Cells(iRow + 1, iCol).Value = ws - 1
Range("B1").Select
End Sub

See you reverted back to selecting and activecell

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Jim May]

Thanks Bob;
Yeah, using the activecell as I have is the least professional part of the
code.
I prefer assigning the cumulative-type sequence to the sheets, for now so
how
could I get around referring to the activecell.Offset(iRow - 1, -1) = ws -
1
in my last posted code? To know this will 'bring-me-up-a-notch" in the
never-ending learning process.
Thanks,
Jim

 

[Bob Phillips]

Jim,

This should do it. Just use incrementing counters iCol and iRow, and use
those counters rather than Offset and activating

Sub ListWSNames()
Dim ws As Integer
Dim iCol As Long, iRow As Long
With Worksheets("Index")
.Cells.ClearContents
iCol = 2: iRow = 1
For ws = 2 To Worksheets.Count
.Cells(iRow, iCol) = Worksheets(ws).Name
.Cells(iRow, iCol - 1) = ws - 1
iRow = iRow + 1
If iRow Mod 21 = 0 Then
iCol = iCol + 2
iRow = 1
End If
Next
End With
Range("B1").Select
End Sub

--

HTH

RP
(remove nothere from the email address if mailing direct)