excel - copying between sheets in macro

[Lee_S]

I have a workbook of sheets that contain data and then also a reports sheet which i want to use to pull data from the other sheets and list it.

So, the idea is to scan through column E in each sheet and where the cell is empty copy the entire row to the next line available on the reports sheet.

This sorta works, but only when 'reports' isnt the active sheet, and then it lists all the other rows after the needed ones. This probably isnt the best way to do it, is there a better way so that it doesnt matter which sheet is active? I'd quite like to put this into a button macro on the reports sheet.

Code so far:

Sub Show_Outstanding()
Dim ws As Worksheet
Dim rng As Range
Dim lLastRow As Integer
Dim lLastCol As Integer
Dim x As Integer
 
    For Each ws In Worksheets
        If ws.Name <> "Reports" Then
            Set rng = Worksheets(ws.Name).Range("A1").SpecialCells(xlCellTypeLastCell)
            lLastRow = rng.Row
            lLastCol = rng.Column
            
            For x = 2 To lLastRow
                If Cells(x, "E") = "" Then
         
                Worksheets("Reports").Rows(FindBottomRow("Reports", 1)).Value = Worksheets(ws.Name).Rows(x).Value
         
                End If
            Next x
        End If
    Next
End Sub