VBA not excluding specific data based on text

[PapaApe21]

Hello,

I have some RAW data in a file which I use through a macro to update another sheet and refresh pivots.

In one of the columns there is 4 different names and I want the macro to produce a report for 3 of these names and exclude any data from the reports which is based on the 4th name.

Lets say the 4 names are as follows

John
Bob
Rich
James

Windows("Named Macro.xlsm").Activate
Sheets("Macro").Select
ActiveCell.Offset(0, 0).Select
Name = ActiveCell.Offset(0, 0).Value
File_Name = ActiveCell.Offset(0, 0).Value
ActiveCell.Offset(1, 0).Select

' Format Copy

Windows("Named Data.xlsx").Activate
Sheets("Named data").Select
Selection.AutoFilter Field:=28, Criteria1:=Name, Operator:=xlAnd, Criteria1:="<> James"

Rows("2:100000").Copy

Windows("Named Report.xlsm").Activate
Worksheets("Named data").Activate
Range("A28").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False


Would the above work? I have tried all different combinations but although its spliting the report based on the other names its also including James's data in it which I dont want.

Thank you,

 

[Rijnsent]

Some ideas to help you along:

• Criteria1:="<> James" -> this has a space in it, you probably want this without the space ( so: "<>James"), otherwise you'll filter for not equal to " James"
• The second criteria, <>James, is already covered by the first... Criteria1 is e.g. "has to be equal to Bob", that means that it's automatically not equal to James, so the second criteria can be ignored
• In general, if you record macros you get a lot of .Select and .Activate in your code. That can cause slow macros and weird errors. If you can, try to rewrite it to remove that part. E.g. Range("A1").Select and Selection.PasteSpecial can become one line: Range("A1").PasteSpecial
• Finally, what you probably want is a loop of some kind:

Dim users() As Variant
users = Array("John", "Bob", "Pete")
For Each u In users
    'Do something here
    Debug.Print u
    If u <> "James" Then
        'only do something here
        
    End If
Next u

'Or, from a sheet
users2 = Worksheets("Blad1").Range("A2:A5").Value
For Each u In users
    'Do something here
    Debug.Print u
    If u <> "James" Then
        'only do something here
        
    End If
Next u