Retrieve only visible Sheets Names to a range

[PVA0724]

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

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

 

[Jonmo1]

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

 

[circledchicken]

Hi,

Is that list of worksheets in your code all the worksheets except 'Control'?

 

[PVA0724]

Hi

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

 

[Jonmo1]

Try

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]

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]

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.