-1

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.

enter image description here]1

My output at "Meal register" is: enter image description here

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

cybernetic.nomad
  • 6,100
  • 3
  • 18
  • 31
Beatriz
  • 93
  • 1
  • 9
  • It would be useful to see sample data and the corresponding output. SIde note, it is worthwhile to [Avoid using select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) in your code – cybernetic.nomad Mar 22 '22 at 14:59
  • @cibernético.nômade please check my question. I added information – Beatriz Mar 22 '22 at 15:10
  • Based on Company name you want a meal registered on that sheet? Does not feel like a locig sollution, what if I made type and I meant Company BB, what if I write company DD? I think you should make a new question and write what you want to achieve, maybe you get some more input on an answer.. – Aldert Mar 22 '22 at 21:26
  • 1
    @aldert thank you for your anwer! I only have 3 companies: AA, BB and CC, pre-defined in a database. In the register meal sheet, it is only necessary to put the number, because the name and company I associated automatically appear (I used vlookup in these 2 variables). What I wanted, was to appear in the company, for example AA, when registering that "AA" and register in the sheet that I made "ListaAA"; when BB appears, the record goes to the "BB List" sheet and the same process for the CC company. In my opinion, there must be some condition in the code... – Beatriz Mar 22 '22 at 21:40

1 Answers1

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
Gove
  • 1,745
  • 10
  • 11
  • 1
    Thanks!! Instead of using "thisworksheet" on line 2, can I use the name of the page? If yes, how is this line? – Beatriz Mar 22 '22 at 22:15
  • 1
    Line 2 does use the name of the worksheet. It does not use "thisworksheet" It uses "thisworkbook" and I think for your environment, you can skip this, so it could be set s = worksheets("List_" & range("K9").value) – Gove Mar 22 '22 at 22:20
  • On that same line (line 2) when running this code gives me an error! What can I change? – Beatriz Mar 22 '22 at 22:25
  • The whole point of line 2 is to figure out what sheet to work with based on the company name from cell K9 – Gove Mar 22 '22 at 22:26
  • What is the error? What value is in K9? If K9 has "AA", do you have a sheet named "List_AA"? – Gove Mar 22 '22 at 22:33