Separate
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
|