Hi,

Can anyone help me with writing a macro that lists all the sheets of of a
workbook to a certain range?

I have this:

Sub test()
Dim NumSheets As Integer
Dim sht As Object
NumSheets = Sheets.Count
For j = 1 To NumSheets
Cells(j + 1, 8) = Sheets(j).Name
Next j
End Sub

But it writes ALL sheetnames, not only the visible.

Thanks in advance, Gert-Jan

Gert-Jan,

You're almost there. Just add a If statement to check if the respective
sheet is visible:

Sub test()
Dim NumSheets As Integer
Dim sht As Object
NumSheets = Sheets.Count
For j = 1 To NumSheets
If Sheets(j).Visible = True Then
Cells(j + 1, 8) = Sheets(j).Name
End If
Next j
End Sub

You could also add a separate row counter to prevent any gaps in the list:

Sub test()
Dim NumSheets As Integer
Dim sht As Object
Dim iRow As Integer
'Set the start row
iRow = 2
NumSheets = Sheets.Count
For j = 1 To NumSheets
If Sheets(j).Visible = True Then
Cells(iRow, 8) = Sheets(j).Name
iRow = iRow + 1
End If
Next j
End Sub

Hope that helps

Best regards

John





"Gert-Jan" wrote in message
...
Hi,

Can anyone help me with writing a macro that lists all the sheets of of a
workbook to a certain range?

I have this:

Sub test()
Dim NumSheets As Integer
Dim sht As Object
NumSheets = Sheets.Count
For j = 1 To NumSheets
Cells(j + 1, 8) = Sheets(j).Name
Next j
End Sub

But it writes ALL sheetnames, not only the visible.

Thanks in advance, Gert-Jan

Sub listvisiblesheets()
mc = 2
For Each ws In Worksheets
If ws.Visible = True Then
Cells(mc, 2) = ws.Name
mc = mc + 1
End If
Next
End Sub

--
Don Guillett
SalesAid Software

"Gert-Jan" wrote in message
...
Hi,

Can anyone help me with writing a macro that lists all the sheets of of a
workbook to a certain range?

I have this:

Sub test()
Dim NumSheets As Integer
Dim sht As Object
NumSheets = Sheets.Count
For j = 1 To NumSheets
Cells(j + 1, 8) = Sheets(j).Name
Next j
End Sub

But it writes ALL sheetnames, not only the visible.

Thanks in advance, Gert-Jan

Hi Don,

Thanks a lot, this works fine!

Gert-Jan

"Don Guillett" schreef in bericht
...
Sub listvisiblesheets()
mc = 2
For Each ws In Worksheets
If ws.Visible = True Then
Cells(mc, 2) = ws.Name
mc = mc + 1
End If
Next
End Sub

--
Don Guillett
SalesAid Software

"Gert-Jan" wrote in message
...
Hi,

Can anyone help me with writing a macro that lists all the sheets of of a
workbook to a certain range?

I have this:

Sub test()
Dim NumSheets As Integer
Dim sht As Object
NumSheets = Sheets.Count
For j = 1 To NumSheets
Cells(j + 1, 8) = Sheets(j).Name
Next j
End Sub

But it writes ALL sheetnames, not only the visible.

Thanks in advance, Gert-Jan

Glad to help.

--
Don Guillett
SalesAid Software

"Gert-Jan" wrote in message
.. .
Hi Don,

Thanks a lot, this works fine!

Gert-Jan

"Don Guillett" schreef in bericht
...
Sub listvisiblesheets()
mc = 2
For Each ws In Worksheets
If ws.Visible = True Then
Cells(mc, 2) = ws.Name
mc = mc + 1
End If
Next
End Sub

--
Don Guillett
SalesAid Software

"Gert-Jan" wrote in message
...
Hi,

Can anyone help me with writing a macro that lists all the sheets of of
a workbook to a certain range?

I have this:

Sub test()
Dim NumSheets As Integer
Dim sht As Object
NumSheets = Sheets.Count
For j = 1 To NumSheets
Cells(j + 1, 8) = Sheets(j).Name
Next j
End Sub

But it writes ALL sheetnames, not only the visible.

Thanks in advance, Gert-Jan

Me too! :)

John

"Don Guillett" wrote in message
...
Glad to help.

--
Don Guillett
SalesAid Software

"Gert-Jan" wrote in message
.. .
Hi Don,

Thanks a lot, this works fine!

Gert-Jan

"Don Guillett" schreef in bericht
...
Sub listvisiblesheets()
mc = 2
For Each ws In Worksheets
If ws.Visible = True Then
Cells(mc, 2) = ws.Name
mc = mc + 1
End If
Next
End Sub

--
Don Guillett
SalesAid Software

"Gert-Jan" wrote in message
...
Hi,

Can anyone help me with writing a macro that lists all the sheets of of
a workbook to a certain range?

I have this:

Sub test()
Dim NumSheets As Integer
Dim sht As Object
NumSheets = Sheets.Count
For j = 1 To NumSheets
Cells(j + 1, 8) = Sheets(j).Name
Next j
End Sub

But it writes ALL sheetnames, not only the visible.

Thanks in advance, Gert-Jan

The problem with yours is that it left blank rows for the hidden sheets
--
Don Guillett
SalesAid Software

"John" wrote in message
...
Me too! :)

John

"Don Guillett" wrote in message
...
Glad to help.

--
Don Guillett
SalesAid Software

"Gert-Jan" wrote in message
.. .
Hi Don,

Thanks a lot, this works fine!

Gert-Jan

"Don Guillett" schreef in bericht
...
Sub listvisiblesheets()
mc = 2
For Each ws In Worksheets
If ws.Visible = True Then
Cells(mc, 2) = ws.Name
mc = mc + 1
End If
Next
End Sub

--
Don Guillett
SalesAid Software

"Gert-Jan" wrote in message
...
Hi,

Can anyone help me with writing a macro that lists all the sheets of
of a workbook to a certain range?

I have this:

Sub test()
Dim NumSheets As Integer
Dim sht As Object
NumSheets = Sheets.Count
For j = 1 To NumSheets
Cells(j + 1, 8) = Sheets(j).Name
Next j
End Sub

But it writes ALL sheetnames, not only the visible.

Thanks in advance, Gert-Jan

Hello Don,

Yes I know. That's why I added the second sub in my post.

Never mind, I can deal with the pain :)

Best regards

John

"Don Guillett" wrote in message
...
The problem with yours is that it left blank rows for the hidden sheets
--
Don Guillett
SalesAid Software

"John" wrote in message
...
Me too! :)

John

"Don Guillett" wrote in message
...
Glad to help.

--
Don Guillett
SalesAid Software

"Gert-Jan" wrote in message
.. .
Hi Don,

Thanks a lot, this works fine!

Gert-Jan

"Don Guillett" schreef in bericht
...
Sub listvisiblesheets()
mc = 2
For Each ws In Worksheets
If ws.Visible = True Then
Cells(mc, 2) = ws.Name
mc = mc + 1
End If
Next
End Sub

--
Don Guillett
SalesAid Software

"Gert-Jan" wrote in message
...
Hi,

Can anyone help me with writing a macro that lists all the sheets of
of a workbook to a certain range?

I have this:

Sub test()
Dim NumSheets As Integer
Dim sht As Object
NumSheets = Sheets.Count
For j = 1 To NumSheets
Cells(j + 1, 8) = Sheets(j).Name
Next j
End Sub

But it writes ALL sheetnames, not only the visible.

Thanks in advance, Gert-Jan

I stand corrected. I guess OP and I didn't see it.

--
Don Guillett
SalesAid Software

"John" wrote in message
...
Hello Don,

Yes I know. That's why I added the second sub in my post.

Never mind, I can deal with the pain :)

Best regards

John

"Don Guillett" wrote in message
...
The problem with yours is that it left blank rows for the hidden sheets
--
Don Guillett
SalesAid Software

"John" wrote in message
...
Me too! :)

John

"Don Guillett" wrote in message
...
Glad to help.

--
Don Guillett
SalesAid Software

"Gert-Jan" wrote in message
.. .
Hi Don,

Thanks a lot, this works fine!

Gert-Jan

"Don Guillett" schreef in bericht
...
Sub listvisiblesheets()
mc = 2
For Each ws In Worksheets
If ws.Visible = True Then
Cells(mc, 2) = ws.Name
mc = mc + 1
End If
Next
End Sub

--
Don Guillett
SalesAid Software

"Gert-Jan" wrote in message
...
Hi,

Can anyone help me with writing a macro that lists all the sheets of
of a workbook to a certain range?

I have this:

Sub test()
Dim NumSheets As Integer
Dim sht As Object
NumSheets = Sheets.Count
For j = 1 To NumSheets
Cells(j + 1, 8) = Sheets(j).Name
Next j
End Sub

But it writes ALL sheetnames, not only the visible.

Thanks in advance, Gert-Jan