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

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

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

ADVERTISEMENT

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.
 
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,151,985
Messages
5,767,443
Members
425,414
Latest member
chwein

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