| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Separate
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Separate
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 Microsoft MVP Excel SalesAid Software "Sal" wrote in message ... 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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Separate
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Separate
Thank you for the lesson friend. I appreciate your pointers and macro.
Thank you. "Don Guillett" wrote: 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 Microsoft MVP Excel SalesAid Software "Sal" wrote in message ... 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| separate two dates from a text string into two separate cells | Excel Discussion (Misc queries) | |||
| I wish to separate city, state, and zip into 3 separate columns | Excel Worksheet Functions | |||
| Open Separate Excel Files in Separate Windows | Excel Discussion (Misc queries) | |||
| Save 2 separate data imports in separate worksheets on the same ex | Excel Worksheet Functions | |||
| Open Excel files in separate sessions, not just separate windows? | Excel Discussion (Misc queries) | |||
Linear Mode
