MAcro to - copy, transpose, text to columns, remove column A, combine all columns in 1

Morning Happy Campers!

Attached is my sample workbook, sheet 1 -> raw data, sheet 2 -> data after my first macro runs, sheet 3 ->desired final result
*Data in the sheet is duplicated as i dont have it all to hand

My actual workbook will have 30 tabs, each doing the same macro. (cant change as 1 tab represents 1 pallet)
Sheet1 -> the way the data in entered here is soemthing we cannot change, we are scanning a 2D barcode, and the scanner itself is not able to take programming, so after it completes the scan cycle, it is preset to 'Tab', which explains why the data on Sheet1 runs from A:X.

So each tab will have data in that (once all barcodes are scanned) that runs from A:X.
The macro i created simply copies A:X and transposes the data, changing it from A:X into A1:A24, and then runs text to columns, to have each number in its own cell, and finally deletes column A (range is now A1:Y24)


Sheet 3 (final result) has all data moved into one column.

Kind regards
galvinpaddy