Select and filter worksheets based on name and value from a list

[VBAProIWish]

Hello All,

Rather than try to type it all out, I came up with a before and after illustration of what I would like to do. Please let me know if there are any questions.
All worksheets are in the same workbook.

Thanks much for any help anyone can provide!

 

[RobP]

Hi,

looks like you've been here long time..... could you update your profile as to which version of Excel you are using in order people may be able to assist you ?

many thanks
Rob

 

[VBAProIWish]

I should add that I want to actually delete the values not matching the filtering criteria but use filtering to accomplish that. Which would only leave rows with "Smith", "Acme", "Blake" like in the example above.

Profile updated, thank you for letting me know!

 

[RobP]

Thanks a lot, just some questions here if I may, as I'm a little confused.

Am I correct in thinking you have the same Purchase Order Data on each sheet name currently ? ie. its a copy that you want to filter just for that page ?
You mention, when the page is "selected" you want it to filter (& now delete unwanted) data. Is this everytime someone clicks on that specific tab ?
If so, how is all the data getting copied to that tab in the first place ? (meaning, why not just copy the data you want, once ?)

Looks like this approach is giving you one sheet per customer .. so the workbook could be huge ?

What would stop you for example having your master sheet as is, then a kind of "master PO sheet" , and a "Customer Sheet". At the top of your customer sheet you put a drop down box linked to your master sheet customer names, and then you have a "FILTER" command on the sheet that acts on that drop down box, and shows you all the data you want ?

The workbook becomes much less wieldly, and very simple for anyone to look at all/customer specific orders depending on the sheet chosen ?

cheers

Rob

 

[VBAProIWish]

Hi Rob,

>Yes, every ws in the list has the exact same data copied from the "Master" ws.
>I need this to be entirely automatic without any input. Just run the code and done.

> I realized after posting that my title is a bit misleading.
Basically, I want to go to every ws on the list from the "Master" ws and delete everything that doesn't have the values in columns B, C and D for that row. Filtering may not be the best solution.

So for Row 2 in the "Master" ws, the worksheet named "Smith, Acme, Blake" would only contain rows with "Smith" in column B, "Acme" in column C and "Blake" in column D.

The worksheet names on the list and the number of worksheets can vary every time I will run this code so ws names can't be referred to in the code.

I have this code below that would delete the un-needed rows from each worksheet but I would need to use it 3 times (once for each column) and I wouldn't be able to refer to any worksheet by name because the ws names and number of worksheets are dynamic...

Sub Keep_Rows_with_x_in_column_C()

Dim nCol As Long
Dim cCol As Long
Dim rCount As Long

    With ActiveSheet
        nCol = Application.Match("Company", .Rows(1), 0)
        cCol = Application.Match("Customer Number", .Rows(1), 0)

        For rCount = Cells(.Rows.Count, cCol).End(xlUp).Row To 2 Step -1
            Select Case .Cells(rCount, nCol).Value
                Case Is <> "Acme"
                    .Rows(rCount).EntireRow.Delete
            End Select
        Next
    End With
  End Sub

Thanks and please let me know if you need any more clarification!

 

[VBAProIWish]

Hello All,
Bumping this thread in hopes someone knows a solution...

 

[RobP]

Hi, sorry for my late reply - but here's some code that will step through your sheets and delete the unwanted rows. Be sure to test on a copy of your worksheet, as deleted rows can't be "un-deleted" !

Hope it helps.
Rgds
Rob

Sub remove_customers()

Dim cnt, count, lrow, lastrow As Long
Dim sheetname, customer, company, owner As String

lrow = Worksheets("Master").Cells(Rows.count, 1).End(xlUp).Row

For cnt = 2 To lrow

    customer = Worksheets("Master").Cells(cnt, 2).Value
    company = Worksheets("Master").Cells(cnt, 3).Value
    owner = Worksheets("Master").Cells(cnt, 4).Value

    sheetname = customer & ", " & company & ", " & owner

    If SheetExists(sheetname) Then 'check you have a customer worksheet of that name in the workbook (using private function below)
        
        lastrow = Worksheets(sheetname).Cells(Rows.count, 1).End(xlUp).Row 'get last row of customer sheet
        
        For count = lastrow To 2 Step -1 'now search through the list to delete unwanted items
            If Worksheets(sheetname).Cells(count, 2) <> customer And Worksheets(sheetname).Cells(count, 3) <> company And Worksheets(sheetname).Cells(count, 4) <> owner Then
                Worksheets(sheetname).Cells(count, 2).EntireRow.Delete
            End If
        Next count
            
        Else
            MsgBox ("Customer '" & sheetname & "' has no sheet")
    End If
    
Next cnt

End Sub

Private Function SheetExists(Tabname) As Boolean
'   Returns TRUE if sheet exists in the active workbook
    Dim x As Object
    On Error Resume Next
    Set x = ActiveWorkbook.Sheets(Tabname)
    If Err = 0 Then SheetExists = True _
    Else SheetExists = False
End Function

 

[RobP]

Apologies, you wanted it to run automatically .. So once you have tried it on your data and are happy with its functionality

The best way is to put the code into a "Module", and replace :

Sub remove_customers()

with

Sub Auto_Open()

This way it runs everytime you open the workbook.

Rgds
Rob

 

[VBAProIWish]

Hi Rob and thanks much for sticking with this!
So I ran the code and the code had ZERO errors, but it deleted all the rows from every worksheet except the title row.

Is any of this case sensitive? I may have "owner", "Owner", "OWNER".
I may have "SMITH", "Smith" or "smith"

If yes, can the code be made case INsEnsitiVE?

Thanks

 

[VBAProIWish]

I realized something else that may have affected this. Even though my example above shows "Customer Name", "Company" and "Owner" in columns B, C and D, the columns won't always be in those locations. Can the code be modified to "look" for those 3 columns in row 1 instead? If yes, that would be great.

On another note, after putting those 3 columns in b, c and d, the code did a little more. This time, it kept all rows with the relevant "Company Name", but kept ALL the rows.

So I think I need to provide another clarification here, sorry!

The worksheet names may be repeated one or more times...

Using Smith as the example, I can have a combination of "Smith" with several companies and several "Owners"
For Example...
"Smith, Acme, Blake"
"Smith, ABC, Kim"
"Smith, ABC Carol"
"Smith ZYX, Kim"
"Smith, ZYX, Missy"

etc, etc...

So if I were on the "Smith, Acme", "Blake" ws, I would only want to keep rows that had ALL 3 criteria.
"Smith, Acme, Blake" - Keep row
"Smith, ABC, Kim" - Delete row
"Smith, ABC Carol"- Delete row
"Smith ZYX, Kim"- Delete row
"Smith, ZYX, Missy"- Delete row

Thanks!