Filtering problem

[willyboy58]

I have a worksheet (“Branch 3”) with the following abbreviated info. I want to filter on the “Fund” column.

Branch Fund Number Name
3 1 1000 Bob
3 1 1111 Steve
3 1 1200 Dennis
3 4 2000 Jason
3 4 2222 Mark
3 4 2333 Curtis

With the following code I filter as desired, and then copy and paste the info for the three employees with Fund 4 (Jason, Mark and Curtis) onto the NOVA worksheet. So far, so good.

But I can’t seem to get my “Branch 3” worksheet to display only the remaining employees (Bob, Steve and Dennis). In other words, cut Jason, Mark and Curtis from the “Branch 3” worksheet. I’ve tried a number of different cut/copy/paste placements in the code, but with no success. I know it has to be simple.


Sub SeparateNovaFromSATL()
'this pro separates the Nova from the SATL clients and pastes them to their own sheet

Dim lRow As Long, origShtSatl As Worksheet
Windows("propnova.xls&quot.Activate 'holds both Prop1 and Nova data
Sheets("Branch 3&quot.Select
'don't need to insert headings b/c they are already there

Set origShtSatl = ActiveSheet
lRow = Range("A65536&quot.End(xlUp).Row

With origShtSatl
.Range("A1:F" & lRow).AutoFilter Field:=1, Criteria1:="4" 'curVal
.Range("A1:F" & lRow).Copy
‘tried CutCopyMode = False in the line above
End With

Worksheets.Add
ActiveSheet.Name = "nova "
Range("A1&quot.Select
Selection.PasteSpecial
Range("A1&quot.Select
Columns("A:F&quot.EntireColumn.AutoFit
Range("A1&quot.Select

Sheets("Branch 3&quot.Select
Selection.AutoFilter Field:=1, Criteria1:="1"
Application.CutCopyMode = False
Selection.AutoFilter
End Sub

As always, TIA

Bill

 

[SkipVought]

Hi,

The Criteria...

Name    Name    Name
<>Bob   <>Steve <>Dennis

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[SkipVought]

oops...
Criteria...

Name    Name    Name
<>Jason <>Mark  <>Curtis

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[DaleWatson123321]

Willy,

I've developed a model for you that uses the BRANCH and FUND as the criteria.

It uses Excel's Advanced Filter to extract your required data to a separate sheet - "at the click of a button".

If you'd like to email me, I'll email the file via return email.

Hope this helps.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[DaleWatson123321]

Willy,

Because you mentioned "Filtering-In-Place", I've added an option to do just that.

An additional observation... Due to the type of data you've described, it appears you're a "prime candidate" for receiving assistance with Excel's "Advanced Filter".

In addition to the model I developed for you, I have other examples that you're welcome to. I'm confident they'll help shed some very useful information on the use of the Advanced Filter. This should help make up for Microsoft's "less than adequate" information on this VERY powerful component of Excel.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca