Retrieve only visible Sheets Names to a range

PVA0724

New Member
Joined
Apr 21, 2012
Messages
48
Hi

I'm trying to retrieve the worksheet names to a specif cell and populate from that point only with the names of the visible sheets, but It keeps returning only the last visible sheet name and not the sheets that are visible. Could someone plase point me to where the code is failling - I'm self learner and start with VBA macros a couple of months so I'm still learning how to identify when the error show up

Code:
Sub Summary()
Dim wks As Worksheet
ultl = Sheets("Control").Cells(1048576, 4).End(xlUp).Row
For i = 31 To ultl
For Each wks In Worksheets(Array("Emergencias  Bancomer", "Emergencias Banamex", "Emergencias Cheques", _
"Rentas Transfer", "Rentas Cheques", "Indemnizaciones", "Indemnizaciones Cheque", "Banamex Automaticos", _
"Cheques", "Bancomer", "Cajas", "Anticipos Empleados", "USD", "EUR", "Secretarias", "Luz", "Agua"))
 
     If wks.Visible Then
     Sheets("Control").Cells(i, 5) = wks.Name
     End If
     
Next wks
Next i
End Sub
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
Try this

Change this line
For i = 31 To ultl
to
i = 31

Remove this line
Next i

AFTER this line
Sheets("Control").Cells(i, 5) = wks.Name
Add this line
i = i + 1
 

PVA0724

New Member
Joined
Apr 21, 2012
Messages
48
Hi

Yes that´s the list of worksheets, control is where I need the data to show up.
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
Try

Rich (BB code):
Sub Summary()
Dim wks As Worksheet
ultl = Sheets("Control").Cells(1048576, 4).End(xlUp).Row
'For i = 31 To ultl '<-- change this line to i = 31
i = 31
For Each wks In Worksheets(Array("Emergencias  Bancomer", "Emergencias Banamex", "Emergencias Cheques", _
"Rentas Transfer", "Rentas Cheques", "Indemnizaciones", "Indemnizaciones Cheque", "Banamex Automaticos", _
"Cheques", "Bancomer", "Cajas", "Anticipos Empleados", "USD", "EUR", "Secretarias", "Luz", "Agua"))
 
     If wks.Visible Then
     'AFter this line
     Sheets("Control").Cells(i, 5) = wks.Name
     'Add the line i = i + 1
     i = i + 1
     End If
     
Next wks
'Next i '<--remove this line
End Sub
 

PVA0724

New Member
Joined
Apr 21, 2012
Messages
48
Hi Jonmo1

Thanks it worked...just a quick clarification for my understanding on this...why I could use the approach based on the For...Next structure for this? Based on the changes you suggest it's obvious that I didn't need the nested For Next...so if it's possible for you to explain when should I use a structure where I need to nest For ... Next. Thanks again for your assistance.
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
Glad to help, thanks for the feedback.

You were actually using 2 different loops, when you only needed 1.
The For i = 31 to ultl creates a numerical veriable, which isn't always easy to work with when looping through sheets.
So I chose to reremove that loop, and use the For Each wks loop, this actually utilizes the worksheets themselves, and not a number.
Then added a loop counter (i = i + 1) that incriments only when the visible criteria was met,
therefor incrimenting the row# where the sheetname is entered onto the Control Sheet

Hope that helps.
 

Forum statistics

Threads
1,186,169
Messages
5,956,342
Members
438,247
Latest member
UZev

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