VBA Select Range to copy based on two variables

DJMXM

New Member
Joined
Jun 19, 2013
Messages
45
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>
Code:
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
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

Teeroy

Well-known Member
Joined
Nov 9, 2012
Messages
2,244
Office Version
  1. 365
Platform
  1. Windows
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.
 
Upvote 0

DJMXM

New Member
Joined
Jun 19, 2013
Messages
45
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
 
Upvote 0

DJMXM

New Member
Joined
Jun 19, 2013
Messages
45
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
 
Upvote 0

Forum statistics

Threads
1,195,692
Messages
6,011,168
Members
441,591
Latest member
elmogm

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top