VBA Select Range to copy based on two variables

[DJMXM]

Hello everyone - I am continuing work on this project and have bumped into another VBA Question. I have a Macro that already works great but now I need to look for another variable to sort it down even further. I think it can be done by setting up a "Case" but still not sure how to do that. It already looks through the range to find all the rows that return a value of "True". Now I need it sort those results so it only returns what has a specified value in Column "AJ". I am looking to do several choices (Listed Here). So the end result will be all the items that return "TRUE" In Column "AI" and are Items that return one of these values per Case "Starters, Appetizers, Soup, Salad, Entree, Dessert, Special" with then copy over in the exact format that is listed in the code below. Thank You again for all of you that have helped me get up to speed with VBA.

For example All the items that are selected with "TRUE" can be narrowed down to all of those that are "Salad" as well - Then copied over in the exact format that is listed in the code below.

What I am doing is breaking out all the different course of food and putting a Header in-between each (That is already built and working) - So I can select all the food from a master list and then put it in order on the "Catering BEO" Sheet with Headers in between

Thank You!

Mike

<tbody>
</tbody>

Sub BEOA4()
Application.ScreenUpdating = False
    Dim wsSource As Worksheet
    Dim wsDest As Worksheet
    Dim FoundX As Range
    Dim FirstFound As String
    Dim lastrow As Long
    Application.ScreenUpdating = False
    Set wsSource = Worksheets("Catering and Rental Worksheet")
    Set wsDest = Worksheets("Catering BEO")
    
' Food Check Box Section
Set FoundX = wsSource.Range("AI3:AI52").Find(What:="TRUE", _
                LookAt:=xlPart, SearchDirection:=xlNext, MatchCase:=False)
    If FoundX Is Nothing Then
        Exit Sub
    Else
        FirstFound = FoundX.Address
        Do
        lastrow = wsDest.Range("I" & Rows.Count).End(xlUp).Row + 1
            If lastrow < 3 Then lastrow = 3
            wsDest.Range("I" & lastrow).Resize(1, 1).Value = FoundX.Offset(0, -7).Resize(1, 1).Value
            wsDest.Range("J" & lastrow).Resize(1, 2).Value = FoundX.Offset(0, -5).Resize(1, 2).Value
            wsDest.Range("L" & lastrow).Resize(1, 1).Value = FoundX.Offset(0, 1).Resize(1, 1).Value
            lastrow = lastrow + 1
            FoundX.Value = "TRUE"
            Set FoundX = wsSource.Range("AI3:AI52").FindNext(FoundX)
        Loop Until FoundX.Address = FirstFound
End If
End Sub

 

[Teeroy]

Hi Mike,

Have you thought about using an Autofilter to do this? You could even apply the autofilter by code, copy the data required, then remove the filter if you don't want it to remain.

 

[DJMXM]

I have never used that before. I will look into it but I am hoping to find a way to build the Macro or have help building the Maco So I can put into a Macro that calls all the others in order

 

[DJMXM]

Ok - I looked it over but that will not work for me since the sheet I am building is being designed to fill in a 2nd sheet by making choices over and over again. I will need to build the VBA - That is the only way this is going to work.

Mike