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

itr674

Well-known Member
Joined
Apr 10, 2002
Messages
1,778
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
 

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
L

Legacy 98055

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

Well-known Member
Joined
Apr 10, 2002
Messages
1,778
TsTom - I reversed the 15 on case 2 and 4 and it worked--thanks a bunch ...
 

Watch MrExcel Video

Forum statistics

Threads
1,123,531
Messages
5,602,202
Members
414,513
Latest member
junbuggle

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top