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
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