How to find the Last Used Row on Each Sheet

[YellowLabPro]

In this loop, I need to change the focus from the ActiveSheet to the rest of the sheets in the workbook.
I refer to the current sheet, the activesheet w/ the variable oWst.
lLrws is the last row variable, LR(oWst, "A") is the function to find the last row.
Can I still use the function, or do I need to use a variable instead?


Function LR(Ws As Worksheet, Col As Variant) As Long
Application.Volatile
If Not IsNumeric(Col) Then Col = Columns(Col).Column()
LR = Ws.Cells(Rows.Count, Col).End(xlUp).Row
End Function



Set oWst = ActiveSheet

For Each Ws In Workbooks("TGSProductsAttrib.xls").Worksheets
lLrws = LR(oWst, "A")
For i = 2 To lLrws
If Not IsEmpty(Cells(i, "D").Value) Then
'If IsEmpty(Cells(i, "G").Value) And Not IsEmpty(Cells(i, "D").Value) Then 'Or Not IsEmpty(Cells(i, "E").Value) Or Not IsEmpty(Cells(i, "F").Value) Then
Cells(i, "G").Value = Range("D1").Value & Cells(i, "D").Value & "; "
End If

If Not IsEmpty(Cells(i, "E").Value) Then
Cells(i, "G").Value = Cells(i, "G").Value & Range("E1").Value & Cells(i, "E").Value & "; "
End If

If Not IsEmpty(Cells(i, "F").Value) Then
Cells(i, "G").Value = Cells(i, "G").Value & Range("F1").Value & Cells(i, "F").Value & "; "
End If
Next i
i = 2
Columns("A:G").AutoFit
Next Ws

[YellowLabPro]

And- even if I change the Function to
lLrwt = Ws.cells(rows.count, "A").end(xlup).row, which does find the last row, changes are only happening on the ActiveSheet.
So I suppose my next ? is how do I tell the the code to activate the next sheet in the For...Each so the code performs the instructions on that next sheet?

[mdmackillop]

Hi Doug,
You can simply pass WS to the function to get the last row in column A for each sheet, but you need to carefully specify the sheet where you are reading writing your data.
For Each Ws In Workbooks("TGSProductsAttrib.xls").Worksheets
lLrws = LR(ws, "A")