Separate

[Sal]

I want to change this part of the macro “For Each ws In
ThisWorkbook.Worksheets†(I think) so that Sheet2, Sheet3, Sheet4, and Sheet5
will be excluded from the macro below. Can you tell me how I would do that?

Sub Separate ()
Sheets.Add.Name = "C"
Sheets.Add.Name = "I"
Sheets("sheet1").Activate
Range("A1:L1").Copy
Sheets("C").Activate
Range("A1").PasteSpecial
Sheets("I").Activate
Range("A1").PasteSpecial

Dim lr As Long
Dim ws As Worksheet

Application.EnableEvents = False
Application.ScreenUpdating = False
lr = Sheets("sheet1").Range("D" & Rows.Count).End(xlUp).Row
For Each ws In ThisWorkbook.Worksheets
If ws.Name <> "sheet1" Then
With Sheets("sheet1").Rows("1:" & lr)
..AutoFilter Field:=4, Criteria1:=ws.Name
..Offset(1).Copy Destination:=ws.Range("A2:L65000")
..AutoFilter
End With
End If
Next ws
Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub

 

[Joel]

Sub Separate ()
Sheets.Add.Name = "C"
Sheets.Add.Name = "I"
Sheets("sheet1").Activate
Range("A1:L1").Copy
Sheets("C").Activate
Range("A1").PasteSpecial
Sheets("I").Activate
Range("A1").PasteSpecial

Dim lr As Long
Dim ws As Worksheet

Application.EnableEvents = False
Application.ScreenUpdating = False
lr = Sheets("sheet1").Range("D" & Rows.Count).End(xlUp).Row
For Each ws In ThisWorkbook.Worksheets

If ws.Name <> "sheet1" and _
ws.Name <> "sheet2" and _
ws.Name <> "sheet3" and _
ws.Name <> "sheet4" and _
ws.Name <> "sheet5" Then

With Sheets("sheet1").Rows("1:" & lr)
.AutoFilter Field:=4, Criteria1:=ws.Name
.Offset(1).Copy Destination:=ws.Range("A2:L65000")
.AutoFilter
End With
End If
Next ws
Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub

 

[Don Guillett]

One way that is easy for you to understand and adapt is

Sub ifnotsheets()
For Each ws In Worksheets
If ws.Name <> "Sheet1" And _
ws.Name <> "Sheet2" Then
MsgBox ws.Name
End If
Next
End Sub

You need some clean up here and I don't think you need to disable events.

Sub Separate ()'UNtested
Sheets.Add.Name = "C"
Sheets("sheet1").Range("A1:L1").Copy Range("A1")

Sheets.Add.Name = "I"
Sheets("sheet1").Range("A1:L1").Copy Range("A1")

Dim lr As Long
Dim ws As Worksheet

'Application.EnableEvents = False
Application.ScreenUpdating = False
lr = Sheets("sheet1").Range("D" & Rows.Count).End(xlUp).Row
For Each ws In ThisWorkbook.Worksheets
If ws.Name <> "sheet2" and _
ws.Name <> "sheet3" and _
ws.Name <> "sheet4" and _
ws.Name <> "sheet5" and _
ws.Name <> "sheet6" and _
Then
With Sheets("sheet1").Rows("1:" & lr)
..AutoFilter Field:=4, Criteria1:=ws.Name
..Offset(1).Copy Destination:=ws.Range("A2:L65000")
..AutoFilter
End With
End If
Next ws
'Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

 

[Don Guillett]

Joel, Didn't see yours before I posted

 

[Sal]

Thank you amico mio. This is very helpful to me.

Sub Separate ()
Sheets.Add.Name = "C"
Sheets.Add.Name = "I"
Sheets("sheet1").Activate
Range("A1:L1").Copy
Sheets("C").Activate
Range("A1").PasteSpecial
Sheets("I").Activate
Range("A1").PasteSpecial

Dim lr As Long
Dim ws As Worksheet

Application.EnableEvents = False
Application.ScreenUpdating = False
lr = Sheets("sheet1").Range("D" & Rows.Count).End(xlUp).Row
For Each ws In ThisWorkbook.Worksheets

If ws.Name <> "sheet1" and _
ws.Name <> "sheet2" and _
ws.Name <> "sheet3" and _
ws.Name <> "sheet4" and _
ws.Name <> "sheet5" Then

With Sheets("sheet1").Rows("1:" & lr)
.AutoFilter Field:=4, Criteria1:=ws.Name
.Offset(1).Copy Destination:=ws.Range("A2:L65000")
.AutoFilter
End With
End If
Next ws
Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub

 

[Sal]

Thank you for the lesson friend. I appreciate your pointers and macro.
Thank you.