Thread: Separate
View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.programming
Sal Sal is offline
external usenet poster
 
Posts: 84
Default 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