|
How to incorporate Loop feature
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:D, then after 20 down (C1:D20) 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 |
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 May" wrote in message news:PWfwd.5420$jn.3650@lakeread06... 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:D, then after 20 down (C1:D20) 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 |
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" wrote in message ... 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 May" wrote in message news:PWfwd.5420$jn.3650@lakeread06... 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:D, then after 20 down (C1:D20) 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 |
Jim,
You are right. Thanks Bob "Jim Cone" wrote in message ... 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" wrote in message ... 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 May" wrote in message news:PWfwd.5420$jn.3650@lakeread06... 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:D, then after 20 down (C1:D20) 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:
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" wrote in message ... Jim, You are right. Thanks Bob "Jim Cone" wrote in message ... 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" wrote in message ... 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 May" wrote in message news:PWfwd.5420$jn.3650@lakeread06... 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:D, then after 20 down (C1:D20) 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 |
| All times are GMT +1. The time now is 08:20 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com