How can i record conditional data in a list with vba code?

Question

Following this creation : How to register an order in a line below the last line with data?

Currently, I need to separate by Company. I have this code below:

Sheets("Lista CA").Select
Range("B8:D8").Select
ActiveCell.FormulaR1C1 = _
    "=IF(Meal_register!R8C11=""CA"",Meal_register!R6C3,"""")"
Range("E8:I8").Select
ActiveCell.FormulaR1C1 = _
    "=IF(Meal_register!R8C11=""CA"",Meal_register!R9C3,"""")"
Range("P8:Q8").Select

I just need to register the "Number", "Name" and "Value". However, if at sheet "Meal Register" the company=AA, must register in the sheet "List AA"; if company=BB, must register in the sheet "List BB", and if company=CC, must register in the sheet "List CC". Should register in the line after the last line with data List AA, List BB and List CC have the same layout.

]1

My output at "Meal register" is:

in this case, as the employee is from the AA company, he will register in the "AA list":

Answer 1

To save the data from the "Meal Register" page to the correct sheet, you'll want something like this. I'm typing the VBA here, so It's not tested, but you should see the process. The example assumes the meal register is the active sheet

You should definitely set up data validation on cell K9 to be sure your company names are entered correctly.

dim s as worksheet
set s = thisworkbook.worksheets("List_" & range("K9").value)

dim row as long
row = s.cells(s.rows.count, "B").end(xlup).row + 1

s.cells(row,"B").value = range("C7").value
s.cells(row,"E").value = range("C9").value
s.cells(row,"P").value = range("Q21").value
s.cells(row,"S").value = range("Q3").value