Indirect Funtion to retrieve last cell with Data

[PVA0724]

Hi

Thanks to the help I was able to built a code that retreives the worksheet name in column 5, now I was looking -but I din't find - a way to have the Indirect funtion to point the sheet name and in addition to retrieve the last cell with data in a specific column (that changes based on the worksheet).

Any assistance will be more than welcome

The code I have is this one

Sub Summary()
Dim wks As Worksheet
ultl = Sheets("Control").Cells(1048576, 4).End(xlUp).Row
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
     Sheets("Control").Cells(i, 5) = wks.Name
     Sheets("Control").Cells(i, 6) = ' Application.Worksheetfuntion.Indirect (cells(i, 5) & last active cell of column x from the visible workshet
     i = i + 1
     End If
     
Next wks
End Sub

 

[circledchicken]

Hi,

I think you mentioned in your other post that the array was a list of all your worksheets except "Control". If so you can perhaps simplify it to something like this:

Sub example()

    Dim wks As Worksheet
    Dim i   As Long


    i = 31 ' start row
    For Each wks In Worksheets
        With Sheets("Control")
            If wks.Name <> .Name Then
                If wks.Visible = xlSheetVisible Then
                    .Cells(i, 5) = wks.Name
                    .Cells(i, 6) = wks.Cells(wks.Rows.Count, 5).End(xlUp).Value ' change to .Row if you want the row number rather than cell value
                    i = i + 1
                End If
            End If
        End With
    Next wks


End Sub

Note that this will return the cell value in row 1 if the whole column is empty so you will need to tweak it a little if you need to handle that situation differently.

 

[PVA0724]

Hi Circled

Thank you so much, just what I need.

 

[circledchicken]

You're welcome - thanks for the feedback.