Situation:

2 or 3 dimensional data (like in Pub_1, Pub_2 ...) save space, but are not a good source for database reports. Favorable would be data like in Liste:

        A        B        C        D        E    
  1              Beer     Wine    Juice          
  2 Point A    123,50    98,00                   
  3 Point B     78,50    61,00    42,00          
  4            A        B        C        D        E    
  5      1              Milk     Beer     Wine          
  6      2 Point D      5,00   176,40     5,00          
  7      3 Point B             161,00    23,00          
  8      4            A        B        C        D      E    
\Pub_1/  5      1 Selling  Drink    Turnover Pub             
         6      2 Point A  Beer       123,50 Pub_1           
         7      3 Point A  Wine        98,00 Pub_1           
         8      4 Point A  Juice        0,00 Pub_1           
       \Pub_2/  5 Point B  Beer        78,50 Pub_1           
                6 Point B  Wine        61,00 Pub_1           
                7 Point B  Juice       42,00 Pub_1           
                8 Point D  Milk         5,00 Pub_2           
                9 Point D  Beer       176,40 Pub_2           
              \Liste/ 
     A    B                           C                        
  1 ZE    2 maximum rows per sheet (here: number of points)    
  2 SP    3 maximum columns per sheet (here: number of drinks) 
  3 QZ      rows before range to be converted                  
  4 QS      columns before range to be converted               
  5 ZZ    1 rows before range in Liste to be kept free         
  6 ZT      columns before range in Liste to be kept free      
  7 NA Pub_ name body of sheets for automatic including (NU=0) 
  8 NU    1 position n° of sheet to be included (NU>0)         
\Parameter/

Solution:

Name =INDEX(GET.WORKBOOK(1),INT((ROW()-1-ZZ)/ZE/SP)+NU) as MP,
name =MID(MP,FIND("]",MP)+1,99) as BL,
give name Parameter!A1:A8 to the right B1:B8.

In Liste!A1: fill down (2 columns * 3 rows * 2 sheets = 12 rows) and fill right (4 columns):

=IF(COLUMN()-ZT<4,INDIRECT(IF(NU,BL,NA&INT((ROW()-1-ZZ)/ZE/SP)+1)&"!"
&ADDRESS((MOD(INT((ROW()+SP-1-ZZ)/SP)-1,ZE)+1)*(COLUMN()-ZT<>2)+1+QZ,
(MOD(ROW()+SP-1-ZZ,SP)+1)*(COLUMN()-ZT>1)+1+QS)),IF(NU,BL,INT((ROW()-1-ZZ)/ZE/SP)+1))

Overwrite row 1:1 with field names. ZZ (see Parameter above) is already 1 for it.

Remarks:

The formula's length of 223 is for the purpose "one does it all". You control it via the sheet "Parameter". The parameters can also be used on f.e. Liste!F1:G8 directly, so you can see changes instantly on the same sheet.

The dimension "Sheets" is the outermost treated one in the formula. That means you do not need to parameterize, only to copy down far enough. #REF! indicates the end and can be kept or deleted. If you want to use a simpler only-2D-formula, see here.

If there was only Pub_1 to be shown in Liste, you would only have to copy down the formula 6 rows. The inclusion of sheets works as follows:

a) NU=0. The sheets must have the same name body (Pub_) and an appended N° (1, 2 ...) on it.
b) Excel 4+ NU=1. The formula grabs all sheets up from map position 1. With NU=2, the first sheet is not included.

Define MP and BL only in case you need b). In other words: Spreadsheets with no formula naming still can use a). For this possibility, naming parts of formulas like INT((ROW()-1-ZZ)/ZE/SP)+1 (occurring 3 times) is also not done.

COLUMN() is only for the flat treatment of each dimension. ROW() would be enough in favor of individual, shorter, but inflexible formulas.