Macro to transfer data from single to multiple columns

Closed
Report
Posts
1
Registration date
Thursday October 1, 2015
Status
Member
Last seen
October 1, 2015
-
Posts
2829
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
November 22, 2021
-
So I have been doing research but have been unable to find a macro that does what I need. Basically, I have multiple items on different carts, labeled with letters, which aren't necessarily in order. I scan the cart and then the items on it, scan the next cart and its items and so on. When I do this, it puts the data into a single column in excel looking like this:


What I need is a macro that will recognize when I scan a new cart and put it in a new column, looking like this:


And, if possible, it would be nice if the columns were arranged alphabetically, like this:


Any help would be greatly appreciated as I am not familiar with macros

1 reply

Posts
2829
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
November 22, 2021
490
Hi NWF,

Since every new scan will be put in the A column, column A can't be used as shown in your second pic.

Try the following code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Columns("A:A")) Is Nothing Then Exit Sub
Dim lCol As Integer

If IsNumeric(Target.Value) = False Then
    Cells(1, Columns.Count).End(xlToLeft).Offset(0, 1).Value = Target.Value
Else
    lCol = Cells(1, Columns.Count).End(xlToLeft).Column
    Cells(Rows.Count, lCol).End(xlUp).Offset(1, 0).Value = Target.Value
End If
End Sub


Best regards,
Trowa