VBA dynamic last row for multiple sheets

[JustTheTim]

Ok so I'm a bit frustrated with several failed attempts at this. I have a workbook with many worksheets. I have code to loop through each sheet (except one) and perform different calculations for certain rows. I then take those calculations and attempt to paste them 3 and 4 rows after the last row. It works perfectly for the first sheet but the subsequent sheets end up with the calculations pasted in the same rows as the first sheet. It's as if it's only finds the last row number once then stores it for the rest of the loop.

Any and all help would be greatly appreciated!

Sub WorksheetLoop()

Dim lr As Long
Dim sheet As Worksheet
Dim avd As Worksheet

Set avd = ThisWorkbook.Worksheets("Calendar")

For Each sheet In ActiveWorkbook.Worksheets
If sheet.Name <> avd.Name Then
With sheet

lr = Cells(Rows.Count, 1).End(xlUp).Row

.Range("A1:F" & lr).AutoFilter Field:=6, Criteria1:="X"
.Range("T5").Value = WorksheetFunction.Average(sheet.Columns("C").SpecialCells(xlCellTypeVisible))
.Range("U5").Value = WorksheetFunction.Average(sheet.Columns("D").SpecialCells(xlCellTypeVisible))
.Range("V5").Value = WorksheetFunction.Average(sheet.Columns("E").SpecialCells(xlCellTypeVisible))
.Range("W5").Value = (WorksheetFunction.CountA(sheet.Columns("A").SpecialCells(xlCellTypeVisible)))-1
.ShowAllData

.Range("A1:F" & lr).AutoFilter Field:=6, Criteria1:=""
.Range("X6").Value = WorksheetFunction.Average(sheet.Columns("C").SpecialCells(xlCellTypeVisible))
.Range("Y6").Value = WorksheetFunction.Average(sheet.Columns("D").SpecialCells(xlCellTypeVisible))
.Range("Z6").Value = WorksheetFunction.Average(sheet.Columns("E").SpecialCells(xlCellTypeVisible))
.Range("AA6").Value = (WorksheetFunction.CountA(sheet.Columns("A").SpecialCells(xlCellTypeVisible)))-1
.ShowAllData

.Range("X6:AA6").Cut Destination:=sheet.Range("C" & lr).Offset(3,0)
.Range("T5:W5").Cut Destination:=sheet.Range("C" & lr).Offset(1,0)
End With
End If
Next sheet
End Sub

 

[juddaaaa]

This line...

lr = Cells(Rows.Count, 1).End(xlUp).Row

...should be...

lr = .Cells(.Rows.Count, 1).End(xlUp).Row

(Notice the "." in front of "Cells" and "Rows")

Sub WorksheetLoop()

    Dim lr As Long
    Dim sheet As Worksheet
    Dim avd As Worksheet
    
    Set avd = ThisWorkbook.Worksheets("Calendar")
    
    For Each sheet In ActiveWorkbook.Worksheets
        If sheet.Name <> avd.Name Then
            With sheet
                lr = .Cells(.Rows.Count, 1).End(xlUp).Row
                
                .Range("A1:F" & lr).AutoFilter Field:=6, Criteria1:="X"
                .Range("T5").Value = WorksheetFunction.Average(sheet.Columns("C").SpecialCells(xlCellTypeVisible))
                .Range("U5").Value = WorksheetFunction.Average(sheet.Columns("D").SpecialCells(xlCellTypeVisible))
                .Range("V5").Value = WorksheetFunction.Average(sheet.Columns("E").SpecialCells(xlCellTypeVisible))
                .Range("W5").Value = (WorksheetFunction.CountA(sheet.Columns("A").SpecialCells(xlCellTypeVisible))) - 1
                .ShowAllData
                
                .Range("A1:F" & lr).AutoFilter Field:=6, Criteria1:=""
                .Range("X6").Value = WorksheetFunction.Average(sheet.Columns("C").SpecialCells(xlCellTypeVisible))
                .Range("Y6").Value = WorksheetFunction.Average(sheet.Columns("D").SpecialCells(xlCellTypeVisible))
                .Range("Z6").Value = WorksheetFunction.Average(sheet.Columns("E").SpecialCells(xlCellTypeVisible))
                .Range("AA6").Value = (WorksheetFunction.CountA(sheet.Columns("A").SpecialCells(xlCellTypeVisible))) - 1
                .ShowAllData
                
                .Range("X6:AA6").Cut Destination:=sheet.Range("C" & lr).Offset(3, 0)
                .Range("T5:W5").Cut Destination:=sheet.Range("C" & lr).Offset(1, 0)
            End With
        End If
    Next sheet

End Sub

 

[JustTheTim]

Wow...such simple errors. THANK YOU!!