Macro to transfer data from single to multiple columns Closed

Question

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

TrowaD · Answer

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

Macro to transfer data from single to multiple columns

1 response

Similar discussions

Subscribe To Our Newsletter!