Worksheet - how to get WS to list in different sections of s

[itr674]

I am using the following code to list worksheets, and headings for the sheets, but I want the list to fit on one page--currently they go across the sheet to far.

I want Test1 heading in A1 and worksheets down column A, and Test2 heading in A15 and worksheets down column A. Then Test3 heading in B1 and worksheets down column B, and Test4 heading in B15 and worksheets down column B. -- hope this makes sense ...<pre>Sub WS_List()
Dim ws As Worksheet, wsName As String
Dim colA As Integer, colB As Integer, colC As Integer, colD As Integer, _
colE As Integer, colF As Integer, colG As Integer, colH As Integer

colA = 2
colB = 2
colC = 2
colD = 2
colE = 2
colF = 2
colG = 2
colH = 2

With Sheet2.Cells
.Range("A:H").Clear
.Font.Name = "Arial"
.Font.Size = 8
.Range("A1:H1").Font.Bold = True
End With


For Each ws In ThisWorkbook.Sheets
wsName = Mid(ws.Name, 1, 3)
Select Case wsName
Case "fil"
Sheet2.Cells(1, 1).Value = "Test1"
Sheet2.Cells(colA, 1).Value = ws.Name
colA = colA + 1
Case "xpl"
Sheet2.Cells(1, 2).Value = "Test2"
Sheet2.Cells(colB, 2).Value = ws.Name
colB = colB + 1
Case "udm"
Sheet2.Cells(1, 3).Value = "Test3"
Sheet2.Cells(colC, 3).Value = ws.Name
colC = colC + 1
Case "inp"
Sheet2.Cells(1, 4).Value = "Test4"
Sheet2.Cells(colD, 4).Value = ws.Name
colD = colD + 1</pre>
This message was edited by em on 2002-10-12 19:56
This message was edited by em on 2002-10-12 19:58
This message was edited by em on 2002-10-18 01:30

 

[Legacy 98055]

<pre>
colA_1 = 2
colA_2 = 16
colB_1 = 2
colB_2 = 16
'ect...

With Sheet2.Cells
.Range("A:H").Clear
.Font.Name = "Arial"
.Font.Size = 8
.Range("A1:H1").Font.Bold = True
End With


For Each ws In ThisWorkbook.Sheets
wsName = Mid(ws.Name, 1, 3)
Select Case wsName
Case "fil"
Sheet2.Cells(1, 1).Value = "Test1"
Sheet2.Cells(colA_1, 1).Value = ws.Name
colA_1 = colA_1 + 1
Case "xpl"
Sheet2.Cells(1, 15).Value = "Test2"
Sheet2.Cells(colA_2, 1).Value = ws.Name
colA_2 = colA_2 + 1
Case "udm"
Sheet2.Cells(2, 1).Value = "Test3"
Sheet2.Cells(colB_1, 2).Value = ws.Name
colB_1 = colB_1 + 1
Case "inp"
Sheet2.Cells(2, 15).Value = "Test4"
Sheet2.Cells(colB_2, 2).Value = ws.Name
colB_2 = colB_2 + 1
</pre>

Tom

 

[itr674]

TsTom - I reversed the 15 on case 2 and 4 and it worked--thanks a bunch ...