How do I copy cells and remove blank?

attach excel file with data and expected result. please don't expect others to create it for you. you'll get help if you do that.

What modytrane is requesting is that you upload a sample workbook showing what you want, not just a picture. We can't experiment with pictures and would therefore have to type up sample data to work with. The results will need to be manually placed in the sample so that we can attempt to automate those results with either formulas or code. To upload a sample workbook click on the GO ADVANCED button below the Quick Reply window and then scroll down to Manage Attachments to open the upload window.
Let us know if you have any questions.

I know that you are looking for a VBA solution, however since this has not received many responses here is an example of how this might be done using formulas.
1) On Tabelle 1 the recipes are put into records which makes it easier to access the data. Column F, which was not in the original data, is populated using: =IF(ISTEXT(G2),G2,F1)
2) On Tabelle 2 data validation is applied to cell E1 which takes the place of the buttons on Tabelle 1 and which references the list in column N.
3) The array entered formula* which populates column A is:

Formula:

=IFERROR(INDEX(Tabelle1!G$2:G$15,MATCH(1,(Tabelle1!F$2:F$15=E$1)*(Tabelle1!H$2:H$15=C4),0)),0)

4) On Tabelle 3 the array entered formula* that populates columns A and B is:

Formula:

=IFERROR(INDEX(Tabelle2!B$3:B$23,SMALL(IF(Tabelle2!$B$3:$B$23>0,ROW($3:$23)-2),ROW(1:1))),"")

*Array entered formulas are confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
In the future please upload files directly to this site. (directions in post #4)
Let us know if you have any questions.

Thank you very much. But I have 1 problem:

I want to work in Tabelle2 "Recipe (all ingredients)" that is where I change the % in the cells. But when i write in it for example 20 % for B the whole thing doesn't work anymore, because I overwrote the code.


Can you explain me why it is =IF(ISTEXT(G2),G2,F1) and not =IF(ISTEXT(G2),G2,F2)? I don't understand that over the corner thing.

I can see that when you overwrite cell A4 on Tabelle2 it changes the other values, however without knowing what you would expect the other values to be I can't offer much in the way of help. If you change A4 to 20% what do you expect to see in cells B3, B4 and B5? An explanation as to why you would expect to see those values would also be helpful.
The formula is set up so that if there is text in the adjacent cell, in column G, it will copy that text, otherwise it will copy the text from the cell above, in column F. I hope that makes sense.
I don't understand to what "over the corner" is referring?

As to the "switch between "alpha" and "beta"...[so that they]... go back to the original recipe". That will probably take VBA or possibly a template, I have asked some of the contributors from the VBA to take a look.
As to the more detailed form, I feel the easiest (perhaps only) way to accomplish that is to add the details to the "All Ingredients" list, so that for example instead of water you would have hot water and cold water. I know if will make the list longer, however there are 1,048,576, and possibly more in the 365 version, rows.
Let us know if you have any questions.

See if you can live with this.
Changing datavalidation triggers code.

In that case you would need 2 seperate codes.
1 in sheet1 to send recipy to sheet2 and another one in sheet2 to transfer adapted recipy to sheet3.

Changing validation changes values on Tabelle2. Code redirects to Tabelle2.
After making changes (or not) pushing button transfers values to Tabelle3. Code redirects to Tabelle3.

Perhaps use the following array entered formula* to populate table 4 as in:

Formula:

=IFERROR(INDEX(Rechner!E$3:E$16,SMALL(IF(Rechner!$E$3:$E$16>0,ROW($3:$16)-2),ROW(1:1))),"")

1) Select cell H21,
2) Paste the formula into the formula bar,
3) Simultaneously press the Ctrl, Shift and Enter keys,
4) Double click the fill handle to copy down to H31
5) While the range H21:H31 is still selected drag the fill handle of over to I31.
Let us know if you have any questions.

Here is a copy of the file with the formula applied as described.
Let us know if you have any questions.