Haklısınız Hocam aşağıdaki şekilde olması gerekir.

For Z = 2 To Sheets.Count
            If LCase(Sheets(Z).Name) <> "sayfa1" And LCase(Sheets(Z).Name) <> "liste" Then
            .Cells(.Range("A" & Rows.Count).End(3).Row + 1, 1) = Sheets(Z).[a1].Value
            .Cells(.Range("A" & Rows.Count).End(3).Row, 2) = Sheets(Z).[H5].Value
            .Cells(.Range("A" & Rows.Count).End(3).Row, 3) = Sheets(Z).[J5].Value
            .Cells(.Range("A" & Rows.Count).End(3).Row, 4) = Sheets(Z).[L4].Value
            End If
        Next