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

Option Explicit

Sub test()
    Dim ws As Worksheet, a, b, i As Long, n As Long, flg As Boolean
    For Each ws In Worksheets
        If ws.Visible = -1 Then
            With ws.Range("a1").CurrentRegion
                a = .Rows(1).Value
                .ClearContents
                ReDim b(1 To 100): n = 0
                With CreateObject("VBScript.RegExp")
                    .Pattern = "\b\d+\b"
                    Do
                        flg = False
                        For i = 1 To UBound(a, 2)
                            If .test(a(1, i)) Then
                                n = n + 1: flg = True
                                If n > UBound(b) Then
                                    ReDim Preserve b(1 To UBound(b) + 100)
                                End If
                                b(n) = .Execute(a(1, i))(0)
                                a(1, i) = .Replace(a(1, i), "")
                            End If
                        Next
                    Loop While flg
                End With
                With .Cells(1).Resize(n, 1)
                    .NumberFormat = "0"
                    .Value = Application.Transpose(b)
                    .Columns.AutoFit
                End With
            End With
        End If
    Next
End Sub

@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...

Option Explicit

Sub test()
    Dim ws As Worksheet, a, b, i As Long, n As Long, flg As Boolean
    For Each ws In Worksheets
        If ws.Visible = -1 Then
            With ws.Range("a1").CurrentRegion
                a = .Rows(1).Value
                .ClearContents
                ReDim b(1 To Rows.Count): n = 0
                With CreateObject("VBScript.RegExp")
                    .Pattern = "\b\d+\b"
                    Do
                        flg = False
                        For i = 1 To UBound(a, 2)
                            If .test(a(1, i)) Then
                                n = n + 1: flg = True
                                b(n) = .Execute(a(1, i))(0)
                                a(1, i) = .Replace(a(1, i), "")
                            End If
                        Next
                    Loop While flg
                End With
                With .Cells(1).Resize(n, 1)
                    .NumberFormat = "0"
                    .Value = b
                    .Columns.AutoFit
                End With
            End With
        End If
    Next
End Sub

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)

ReDim b(1 To Rows.Count, 1 To 1): n = 0

2)

b(n, 1) = .Execute(a(1, i))(0)

fantastic, thx very much!!