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,

yes, I realised I hadn't tested it on "rogue" data .. and I'd "AND" rather than "OR'd" the three column checks.

Let me know how you get on with this one:

Sub Auto_Open()

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

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

 With Worksheets("Master")
        comp_col = Application.Match("Company", .Rows(1), 0)
        cust_col = Application.Match("Customer Name", .Rows(1), 0)
        own_col = Application.Match("Owner", .Rows(1), 0)
End With

For cnt = 2 To lrow

    customer = UCase(Worksheets("Master").Cells(cnt, cust_col).Value)
    company = UCase(Worksheets("Master").Cells(cnt, comp_col).Value)
    owner = UCase(Worksheets("Master").Cells(cnt, own_col).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 UCase(Worksheets(sheetname).Cells(count, cust_col)) <> customer Or UCase(Worksheets(sheetname).Cells(count, comp_col)) <> company Or UCase(Worksheets(sheetname).Cells(count, own_col)) <> 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]

Hi, Sorry, I just realised I was searching the Master sheet for the columns, rather than the Customer Sheets. The caveat here is that ColA MUST have some data inside it, as it uses this to find the last row of data in use. If Col A is blank, or not the same number of rows as your actual data, then you'll have a problem.

Sub Auto_Open()

Dim cnt, count, lrow, lastrow, cust_col, comp_col, own_col 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 = UCase(Worksheets("Master").Cells(cnt, 2).Value)
    company = UCase(Worksheets("Master").Cells(cnt, 3).Value)
    owner = UCase(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)
    
        With Worksheets(sheetname)
            cust_col = Application.Match("Customer Name", .Rows(1), 0)
            comp_col = Application.Match("Company", .Rows(1), 0)
            own_col = Application.Match("Owner", .Rows(1), 0)
        End With
            
        lastrow = Worksheets(sheetname).Cells(Rows.count, 1).End(xlUp).Row 'get last row of customer sheet from Data Col "A". Col A must have data in it.
        
        For count = lastrow To 2 Step -1 'now search through the list to delete unwanted items
        
            If UCase(Worksheets(sheetname).Cells(count, cust_col)) <> customer Or UCase(Worksheets(sheetname).Cells(count, comp_col)) <> company Or UCase(Worksheets(sheetname).Cells(count, own_col)) <> 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

 

[VBAProIWish]

Rob, all I can say is WWwwoowwWW!

You nailed it! Even when the columns are in different positions!
Thank you!

Only one small thing I just noticed...
Post-Macro, things like column widths and vertical alignment in the cells are lost on all the worksheets. Is there a way to keep all the original formatting intact on all the worksheets after the macro is run?

SIDE NOTE:
I do have a question...I noticed you used "Function". I know nothing about "Function" or why it's even necessary. Just out of curiosity and no need to do this as I am very happy with the code as-is, but is it even possible to code this exact same thing without using "Function"?
I'm just trying to learn a little as I go.

Thanks again for all your help with this!

 

[RobP]

Hi, The Function "SheetExists" is just a quick routine to check if the Tabname is a real one in the file. There are few ways to check if a sheet actually exists - a quick google will bring a few solutions for you. I've always used that one, as its simple and "worked for me".

Function is just another piece of code that seperates out a little process, that you can call from the main routine. Sometimes makes code more readable.

Glad to have been of assistance, and thanks for the feedback.

Rob

 

[VBAProIWish]

Thanks for that explanation! I edited my last post likely while you were posting and you probably didn't see it. Is the below something that can be modified fairly easily?

Only one small thing I just noticed...
Post-Your code, things like column widths and vertical alignment in the cells are lost on all the worksheets. Is there a way to keep all the original formatting intact on all the worksheets after the macro is run?

Thanks!

 

[RobP]

That's quite strange, as there is nothing in the code that does anything to your formatting. It simply deletes rows you don't want.

When I run on my dummy (formatted) data, it keeps column widths, and also cell centering, as expected.

I don't see how it can happen with just my code above, sorry.

Rob

 

[VBAProIWish]

Ok, I found a workaround. I just filtered and changed to row height in the middle of your code.

All good and thanks!