I have a workbook that has close to 100 worksheets in it and have come up
with a little this code to list all the worksheet names on a single worksheet
starting at row 2. What I am trying to do is manipulate this code even
further that states to have it list the worksheets from row 2 down to row 30
in column 1, then move over to column 2 and do the same thing from row 2 to
30 and so on till all names have been listed.

Sub ShowNames_Click()
Dim wkbkToCount As Workbook
Dim ws As Worksheet
Dim iRow As Integer
Set wkbkToCount = ActiveWorkbook
iRow = 2

For Each ws In wkbkToCount.Worksheets
ActiveSheet.Rows(iRow).Cells(1).Value = ws.Name

iRow = iRow + 1

Next

Range("A1").Select
End Sub

Kyer,

try this:

Sub ShowNames_Click()
Dim wkbkToCount As Workbook
Dim ws As Worksheet
Dim iRow As Integer, iCol As Integer
Set wkbkToCount = ActiveWorkbook
iRow = 2
iCol = 1
For Each ws In wkbkToCount.Worksheets
ActiveSheet.Rows(iRow).Cells(iCol).Value = ws.Name

iRow = iRow + 1
If iRow 30 Then
iRow = 2
iCol = iCol + 1
End If

Next

Range("A1").Select
End Sub

"Kryer" wrote:

I have a workbook that has close to 100 worksheets in it and have come up
with a little this code to list all the worksheet names on a single worksheet
starting at row 2. What I am trying to do is manipulate this code even
further that states to have it list the worksheets from row 2 down to row 30
in column 1, then move over to column 2 and do the same thing from row 2 to
30 and so on till all names have been listed.

Sub ShowNames_Click()
Dim wkbkToCount As Workbook
Dim ws As Worksheet
Dim iRow As Integer
Set wkbkToCount = ActiveWorkbook
iRow = 2

For Each ws In wkbkToCount.Worksheets
ActiveSheet.Rows(iRow).Cells(1).Value = ws.Name

iRow = iRow + 1

Next

Range("A1").Select
End Sub

This is great, Thanks for the help on this. Do have another question. I have
a worksheet_selectionchange setup:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Value "" Then
Worksheets(ActiveCell.Value).Select
End If
End Sub

Now when I got to highlight the columns to delete the list of worksheets it
gives me an error:

Run Time Error: 13
Type Mismatch.

Is there any way to delete the data with out getting this error? I have
tried vba to ignore the error and continue with the action but I cannot seem
to get it to work.
"Toppers" wrote:

Kyer,

try this:

Sub ShowNames_Click()
Dim wkbkToCount As Workbook
Dim ws As Worksheet
Dim iRow As Integer, iCol As Integer
Set wkbkToCount = ActiveWorkbook
iRow = 2
iCol = 1
For Each ws In wkbkToCount.Worksheets
ActiveSheet.Rows(iRow).Cells(iCol).Value = ws.Name

iRow = iRow + 1
If iRow 30 Then
iRow = 2
iCol = iCol + 1
End If

Next

Range("A1").Select
End Sub

"Kryer" wrote:

I have a workbook that has close to 100 worksheets in it and have come up
with a little this code to list all the worksheet names on a single worksheet
starting at row 2. What I am trying to do is manipulate this code even
further that states to have it list the worksheets from row 2 down to row 30
in column 1, then move over to column 2 and do the same thing from row 2 to
30 and so on till all names have been listed.

Sub ShowNames_Click()
Dim wkbkToCount As Workbook
Dim ws As Worksheet
Dim iRow As Integer
Set wkbkToCount = ActiveWorkbook
iRow = 2

For Each ws In wkbkToCount.Worksheets
ActiveSheet.Rows(iRow).Cells(1).Value = ws.Name

iRow = iRow + 1

Next

Range("A1").Select
End Sub

It errors because the range is a column (multi-cell) rather than one cell

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count 1 Then Exit Sub 'Not single cell
If Target.Value "" Then
Worksheets(ActiveCell.Value).Select
End If
End Sub

"Kryer" wrote:

This is great, Thanks for the help on this. Do have another question. I have
a worksheet_selectionchange setup:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Value "" Then
Worksheets(ActiveCell.Value).Select
End If
End Sub

Now when I got to highlight the columns to delete the list of worksheets it
gives me an error:

Run Time Error: 13
Type Mismatch.

Is there any way to delete the data with out getting this error? I have
tried vba to ignore the error and continue with the action but I cannot seem
to get it to work.
"Toppers" wrote:

Kyer,

try this:

Sub ShowNames_Click()
Dim wkbkToCount As Workbook
Dim ws As Worksheet
Dim iRow As Integer, iCol As Integer
Set wkbkToCount = ActiveWorkbook
iRow = 2
iCol = 1
For Each ws In wkbkToCount.Worksheets
ActiveSheet.Rows(iRow).Cells(iCol).Value = ws.Name

iRow = iRow + 1
If iRow 30 Then
iRow = 2
iCol = iCol + 1
End If

Next

Range("A1").Select
End Sub

"Kryer" wrote:

I have a workbook that has close to 100 worksheets in it and have come up
with a little this code to list all the worksheet names on a single worksheet
starting at row 2. What I am trying to do is manipulate this code even
further that states to have it list the worksheets from row 2 down to row 30
in column 1, then move over to column 2 and do the same thing from row 2 to
30 and so on till all names have been listed.

Sub ShowNames_Click()
Dim wkbkToCount As Workbook
Dim ws As Worksheet
Dim iRow As Integer
Set wkbkToCount = ActiveWorkbook
iRow = 2

For Each ws In wkbkToCount.Worksheets
ActiveSheet.Rows(iRow).Cells(1).Value = ws.Name

iRow = iRow + 1

Next

Range("A1").Select
End Sub