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

Hi, i want the result to be as on sheet 3.
The work book will consist of tabs 1-30 (named pallet1, pallet2 etc)
We will scan into tab pallet 1, and the result for that would stay in pallet 1, then we would repeat the process just on the next tab

Apologies for the hazy description!

Hi, if thats an easier way to work it then yes
I assumed having the data scanned into sheet1, then the macro running on the active sheet, sorting all data into the right format and keeping it all on sheet1 would help keep the work book clutter free.
My sample was loaded on different sheets only to show what i start with, what i had worked on so far and what i wanted to happen at the end of it.

Hi, thanks for that, but that code is the similar to what i already have
I need help with getting all data (after text to columsn is run) to combine all data into Column A.

So run your code on my sheet, i need the code to take all data from all columns and put it all into column A

Thanks alot for your help so far

Hi, your code only needs extending.
Its needs to run what you have already done, and then combine all the data.

When your code (and mine for that matter) are run, the data ends in over 24 columns, i simply want it all to be in column A. (running from A1:A600)

This is my understanding

Please Login or Register  to view this content.

@jindon - > Spot on, thanks an awful lot for that!!

@ Norie - > I do apologise for my somewhat lacking explanations! i am very gratefull for all help!!

Regards

Edit**

Norie - Also spot on, thanks very much indeed to you both!! rep added with gratitude!

Hello again both,

I'm afraid i must re-awaken this thread

@jindon - Your code is not able to handle the amount of data i have, the code fails and removes data at random points
@ Norie - Yours almost does what i need your moves all the data onto one sheet.

In my workbook i have multiple sheets, each sheet relating to 1 pallet. i need the data from sheet 1 to remain on sheet 1, the same for every other sheet in the workbook (2 on 2, 3 on 3 etc etc) as the code currently collates everything onto one sheet, which prevents me from easily identifying what bit of data relates to what pallet

Could i respectfully request a re-visit?

regards

Probably Transpose function...

Please Login or Register  to view this content.

Hi Jindon,

Sorry, but your code is now only showing the same digit!
The raw data is 600 unique numbers, your code takes the first unique number and repeats it 599 times to give me the same number 600 times

Yup! typo

1)

Please Login or Register  to view this content.

2)

Please Login or Register  to view this content.

fantastic, thx very much!!