Select Last visible Sheet

[Dannyh1]

Hi

I have code to select last sheet of a workbook

 Sheets(Sheets.Count).Select

This code will error if sheets a hidden.

Could someone please advise how to adapt this to select last visible sheet in workbook?

Many Thanks

 

[schielrn]

This is one way:

for i = sheets.count to 1 step -1
if sheets(i).visible = -1 then
     sheets(i).select
     exit sub
end if
next i

Hope that helps and this is untested.

 

[Dannyh1]

Thanks schielrn

I think I am positioning this incorrectly in my code though

This is what I have:

 Dim rng As Range
 Set rng = Sheets("Files").Range("A1:A61")
 
 For Each cl In rng
 
 Workbooks.Open Filename:=cl.Value
 
    For i = Sheets.Count To 1 Step -1
    If Sheets(i).Visible = True Then
     Sheets(i).Select
     Exit Sub
    End If
    Next i
 Range("U1").Select
 ActiveCell.FormulaR1C1 = _
        "=MID(CELL(""filename"",RC[-20]),FIND(""]"",CELL(""filename"",RC[-20]))+1,256)"
 
 ActiveWorkbook.Save
 ActiveWorkbook.Close
 
 Next cl

When I run this, it stops before ActiveWorkbook.Save (i.e. only does it for one file)

Can you see where I have gone wrong with my code?

Thanks again

 

[schielrn]

Maybe where I have Exit Sub change to Exit For. This way it doesn't exit out of your routine.

Hope that helps.

 

[Dannyh1]

Excellent - thanks a lot