Formula or Script, to replace manual data formatting?

Question

I have a highly repetitive manual task that I'm trying to automate.

A sheet 'Quantities' with the quantity of Codes I need to generate, based on a ticket type (eg; Adult | 174)

A sheet with the Codes descending, equal to the 'Total' sum value from 'Quantities' sheet

A sheet for each Ticket Type which requires 2 columns;

1. descending ticket type string + numbered values "Adult 1", "Adult 2", "Adult 3", etc, up to the total value of the 'Codes Required' from 'Quantities' sheet. Eg, up to "Adult 174"
2. codes populated from the 'Codes' sheet, up to the value of the codes required for that ticket type, eg, the first 174 codes.

For the next ticket type (Junior), the codes would need to be taken consecutively from the 'Codes' sheet, eg the next 72 codes from rows 175-242.

Here is a worksheet with example data, and the example that I'm looking to generate; https://docs.google.com/spreadsheets/d/11MHzNKtQJJwLM_kFb-SWksTzWP4-fWhPjTwHKe8TksU/edit?usp=sharing

My question would be, am I going about this problem the wrong way, trying to sort it using formulas? And would it be better solved using some sort of Apps Script code? I'm not totally sure where to go, or how to write that kind of script.

Tried various combinations of formula types, but ended up becoming more labour intensive than manually doing the sorting / copying / pasting myself.

Tried recording a Macro, but unsure of how to reference the fixed cells values within the Quantities sheet for range referencing in the script.

Macro EG:

    function adultcodesjuniorcodes() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange('B2').activate();
  spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Adult Codes'), true);
  spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Codes'), true);
  spreadsheet.getRange('A1:A174').activate();
  spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Adult Codes'), true);
  spreadsheet.getRange('B1').activate();
  spreadsheet.getRange('Codes!A1:A174').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);
  spreadsheet.getRange('A1').activate();
  spreadsheet.getCurrentCell().setValue('Adult 1');
  spreadsheet.getRange('A2').activate();
  spreadsheet.getCurrentCell().setValue('Adult 2');
  spreadsheet.getRange('A1:A2').activate();
  spreadsheet.getActiveRange().autoFill(spreadsheet.getRange('A1:A174'), SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES);
  spreadsheet.getRange('A1:A174').activate();
  spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Junior Codes'), true);
  spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Quantities'), true);
  spreadsheet.getRange('B3').activate();
  spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Codes'), true);
  spreadsheet.getRange('A175:A246').activate();
  spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Junior Codes'), true);
  spreadsheet.getRange('B1').activate();
  spreadsheet.getRange('Codes!A175:A246').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);
  spreadsheet.getRange('A1').activate();
  spreadsheet.getCurrentCell().setValue('Junior 1');
  spreadsheet.getRange('A2').activate();
  spreadsheet.getCurrentCell().setValue('Junior 2');
  spreadsheet.getRange('A1:A2').activate();
  spreadsheet.getActiveRange().autoFill(spreadsheet.getRange('A1:A72'), SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES);
  spreadsheet.getRange('A1:A72').activate();
};

Answer 1

If you set a header to each sheet like this

You can write these formulas, in A2:

=arrayformula(A1&" "&sequence(vlookup(A1,Quantities!A1:B7,2,false)))

In B2:

=Indirect("Codes!A"&SUM(INDIRECT("QUANTITIES!B1:B"&MATCH(A1,Quantities!A2:A,0)))+1&":A"&SUM(INDIRECT("QUANTITIES!B1:B"&MATCH(A1,Quantities!A1:A,0))))

I've put them in your sheet ;)