Ignore one sheet and run

[Chandresh]

HI All,

I am using below code to run a macro however facing some issue , could you please help.

what macro does- filters data in all sheets based on certain criteria and copy the filtered data to Summarynew sheet.( below macro should ignore "Summary sheet" and run on all other sheets )other sheet

Sub RunFornew()
Dim xWs As Worksheet
On Error Resume Next
For Each xWs In Worksheets
' for filter
Columns("A:W").Select
    'Selection.AutoFilter
    xWs.Range("$A$2:$W$500000").AutoFilter Field:=22, Criteria1:= _
        "=Notional", Operator:=xlAnd
Next
    
    ' for copying data in summary new
    Dim sh As Worksheet
    Dim DestSh As Worksheet
    Dim lRow As Long
    Dim CopyRng As Range
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With
On Error GoTo Ouch
' Delete the summary sheet if it exists.
    Application.DisplayAlerts = False
       On Error Resume Next
       ActiveWorkbook.Worksheets("SummaryNew").Delete
        On Error GoTo 0
    Application.DisplayAlerts = True
' Add a new summary worksheet.
    Set DestSh = ActiveWorkbook.Worksheets.Add
        DestSh.Name = "Summarynew"

For Each sh In ActiveWorkbook.Worksheets
        If sh.Name <> DestSh.Name Then
   
        With sh
            'edited
            
            Set CopyRng = .Range("A2:W" & .Cells(Rows.Count, 1).End(xlUp).Row)
        End With
    
        lRow = DestSh.Cells(Rows.Count, 1).End(xlUp).Row
        
        If lRow = 1 Then
            CopyRng.SpecialCells(xlCellTypeVisible).Copy DestSh.Range("A" & lRow)
            With DestSh
                Set CopyRng = .Range("X1:X" & .Cells(Rows.Count, 1).End(xlUp).Row)
                    'CopyRng.Value = sh.Name
                    CopyRng.Value = "New"
            End With
        Else
            CopyRng.SpecialCells(xlCellTypeVisible).Copy DestSh.Range("A" & lRow + 1)
            With DestSh
                Set CopyRng = .Range("X" & lRow + 1 & ":X" & .Cells(Rows.Count, 1).End(xlUp).Row)
                    CopyRng.Value = "New"
            End With
        End If
    End If
Next sh
Ouch:
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With


End Sub

 

[davesexcel]

you need an if statement

For Each xWs In Worksheets
if xws.name <> "Summary sheet" then
'your code
end if
next xws

 

[Chandresh]

HI Thanks for your reply , however I am still facing the issue, could you please send me the entire code.

Thanks in advance

 

[Fluff]

When you say

below macro should ignore "Summary sheet" and run on all other sheets

do you mean the newly created sheet called "Summarynew"?
If so your code should already be doing that.
If it's not please explain what is happening.

 

[Chandresh]

When you say

below macro should ignore "Summary sheet" and run on all other sheets

do you mean the newly created sheet called "Summarynew"?
If so your code should already be doing that.
If it's not please explain what is happening.

Hi I have many worksheets in the workbook in that there are 2 different worksheet with name 1) summary
2) summarynew

Above macro should copy the filtered data from all the worksheets (except summary worksheet ) and paste in summarynew worksheet

Thanks

 

[Fluff]

In that case try

 If Sh.Name <> DestSh.Name And Sh.Name <> "Summary" Then

 

[Chandresh]

Thanks for help

 

[Fluff]

You're welcome