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" wrote:

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

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

"Joel" wrote:


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" wrote:

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


-----------------------------------------------------------------------------
Less Spam Better enjoyable experience
Visit : news://spacesst.com