0

I have the below macro setup and running, But it will paste the data from the first sheet as many times the number of sheets. Not the data from each sheet to the sheet report.

Can someone please help me why this happens!

Sub apply_autofilter_across_worksheets()
    Dim xWs As Worksheet
    Dim x As String

    On Error Resume Next

    For Each xWs In Worksheets
        If xWs.Name <> "Report" Then            
            xWs.Range("A1").AutoFilter 14, ">0"                
            xWs.Range("A1", Cells(Rows.Count, "A").End(xlUp)).Resize(, 17).Select                
            Selection.Copy                
            Worksheets("Report").Activate                
            Worksheets("Report").Range("x1").Select                
            Worksheets("Report").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues                            
        End If        
    Next
End Sub
Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
anuprasad
  • 1
  • 1
  • You cannot debug a code that uses `On Error Resume Next` like this. This line just hides **all** error messages, but the errors still occur (you just cannot see them). Remove `On Error Resume Next` and see which errors you get and fix them. • You might benefit from reading [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). • Also you must ask a real question see [Why is “Can someone help me?” not an actual question?](https://meta.stackoverflow.com/a/284237/3219613) – Pᴇʜ Jul 10 '19 at 06:38
  • Thanks PEH, by help i was looking for the answer to this code behaviour as the logic seems true here. I am a novice with excel VBA. – anuprasad Jul 10 '19 at 09:03
  • Sorry I don't get the point of your comment. Is it solved? Was it a question? Please be more clear. – Pᴇʜ Jul 10 '19 at 09:05
  • Have you removed this line `On Error Resume Next` as PEH suggested? – SJR Jul 10 '19 at 10:23

0 Answers0