Filtering and moving problem 1

[willyboy58]

I have the following info on a sheet. “Program” is in cell A1.

Program Number Name Date Job Hours
1 1100 steve 1/1/04 158654 6
1 1471 frank 1/1/04 189 6
1 1596 tom 1/4/04 186 6
4 1400 elroy 1/4/04 181 4
4 1500 wilma 1/5/04 181 6


I want to move the individuals in program #4 (Elroy and Wilma) to their own sheet, “NOVA”, and leave those in program #1 (steve, frank and tom) on this active sheet, which is named “Branch 3”. I am able to move Elroy and Wilma to the new sheet called “NOVA”, but when I look at the “Branch 3” sheet, the Filter arrows are still displayed and Elroy and Wilma are still on the sheet. There should be no Filter arrows and only steve, frank and tom should be on the “Branch 3” sheet like below.

Program Number Name Date Job Hours
1 1100 steve 1/1/04 158654 6
1 1471 frank 1/1/04 189 6
1 1596 tom 1/4/04 186 6

I have the following code.

Sub SeparateNovaFromSatl()

Dim curVal As Long, lRow As Long, origShtSatl As Worksheet

Windows("propxfer.xls&quot.Activate
'don't need to add headers
Sheets("Branch 3&quot.Select
Set origShtSatl = ActiveSheet
lRow = Range("A65536&quot.End(xlUp).Row

For i = 2 To lRow 'assumes you put headers in and data starts at row 2
With origShtSatl
If .Range(&quot;A&quot; & i).value <> .Range(&quot;A&quot; & i - 1).value Then
curVal = .Range(&quot;A&quot; & i).value
.Range(&quot;A1:G&quot; & lRow).AutoFilter Field:=1, Criteria1:=curVal
.Range(&quot;A1:G&quot; & lRow).Copy
Else
End If
End With
Next i
Worksheets.Add
ActiveSheet.Name = &quot;NOVA&quot;
Range(&quot;A1&quot.Select
Selection.PasteSpecial
Range(&quot;A1&quot.Select
Selection.EntireColumn.Delete
Columns(&quot;A:G&quot.EntireColumn.AutoFit
Range(&quot;A1&quot.Select

Application.ScreenUpdating = True
Application.StatusBar = False
End Sub

What am I missing? TIA.

Bill

 

[xlbo]

you are copying the data so it will stay there.Use CUT instead or delete the rows from the original worksheet:

.Range(&quot;A2:G&quot; & lRow).entirerow.delete


To get rid of the filter arrows just use
origShtSatl.autofiltermode = false


Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244