For Each WS in WB - Copy NonBlank Cells in Col 'E'

[MikeL]

Hi,
Have a Workbook (WB) with multiple Worksheets (WS)

How does one copy the NONBLANK contents of Col E in each Worksheet and place into Col A of a New or Summary WS in the same WB?

Thanks in advance.

 

[Michael M]

Hi Mikel
Try this, untested....don't have excel at my workstation at the moment

Sub copyem()
Dim lr As Long, ws As Worksheet, lrs As Long
Application.ScreenUpdating = False
For Each ws In Worksheets
    If ws.Name <> "Summary" Then
        ws.Activate
        lr = Cells(Rows.Count, "E").End(xlUp).Row
        lrs = Sheets("Summary").Cells(Rows.Count, "E").End(xlUp).Row
        With Range("E1:E" & lr)
            .AutoFilter
            .AutoFilter Field:=1, Criteria1:="<>"
            .SpecialCells(xlCellTypeVisible).Copy Destination:=Sheets("Summary").Range("E" & lrs + 1)
        End With
        lrs = Sheets("Summary").Cells(Rows.Count, "E").End(xlUp).Row
    End If
Next ws
Application.ScreenUpdating = True
End Sub

 

[Michael M]

oops, sorry, just noticed a typo

Sub copyem()
Dim lr As Long, ws As Worksheet, lrs As Long
Application.ScreenUpdating = False
For Each ws In Worksheets
    If ws.Name <> "Summary" Then
        ws.Activate
        lr = Cells(Rows.Count, "E").End(xlUp).Row
        lrs = Sheets("Summary").Cells(Rows.Count, "A").End(xlUp).Row
        With Range("E1:E" & lr)
            .AutoFilter
            .AutoFilter Field:=1, Criteria1:="<>"
            .SpecialCells(xlCellTypeVisible).Copy Destination:=Sheets("Summary").Range("A" & lrs + 1)
            .AutoFilter
        End With
    End If
Next ws
Application.ScreenUpdating = True
End Sub

 

[MikeL]

Thanks for the code.
I am getting a 1004 error -AutoFilter method of Range class failed

With Range("E1:E" & lr)
.AutoFilter
.AutoFilter Field:=1, Criteria1:="<>"
.SpecialCells(xlCellTypeVisible).Copy Destination:=Sheets("Summary").Range("A" & lrs + 1)
.AutoFilter
End With

Any reason that AutoFilter is in the code twice?

Thanks

 

[Michael M]

The Autofilter text is in twice....once to turn it on, and once to turn it off at the end.
Try running the code manually, by putting your cursor anywhere in the code and then pressing F8 for each step....the yellow highlighted line will tell us where the error is....hopefully.
Does the dat already have autofilter turned on ? if so turn it off for all sheets.

 

[MikeL]

I ran it thru the debug and made sure there were no filters in any of the WS.

It breaks at the .AutoFilter line with the 1004 error. I can google the error in the meantime.

Mike

 

[Michael M]

'bout the only thing that comes to mind is that there is a sheet with column "E" completely blank.
Would that be happening ?

 

[MikeL]

I have data in each WS, col E.
Unfortunate that is not the solution. I will keep searching about this.

Mike

 

[Michael M]

OK, column "E", hidden maybe ?

 

[MikeL]

Not hidden either.

Mike