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

itr674

Well-known Member
Joined
Apr 10, 2002
Messages
1,782
Office Version
  1. 2016
Platform
  1. Windows
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
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
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,782
Office Version
  1. 2016
Platform
  1. Windows
TsTom - I reversed the 15 on case 2 and 4 and it worked--thanks a bunch ...
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,167,941
Messages
5,856,425
Members
431,812
Latest member
Elastomers

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