Hi Guys,

I hope everyone is well. In the screenshot below, we can see we have data in Column A and Column B. What I would like to do is copy it over into Columns D, E and F while splitting it up.

The 4 Digit code is in relation to a location and the 13 Digit Code is a product code. 

I would like to duplicate the location into Column D, and then copy the 13 digit codes that come after it onto column E. The Quantity (1) against the location can be ignored but I would like to copy the quantity against the 13 digit code in Column F as per the screenshot.

Can anyone help?

Many thanks in advance.

I don't quite understand your screenshot - the quantities in column F don't seem to correspond to those in column B for the same product.

But perhaps this will do what you want:

Sub CopyData()
    Dim s As Long
    Dim t As Long
    Dim m As Long
    Dim sLoc As String
    Application.ScreenUpdating = False
    m = Cells(Rows.Count, 1).End(xlUp).Row
    For s = 1 To m
        If Len(Cells(s, 1).Value) = 4 Then
            sLoc = Cells(s, 1).Value
        Else
            t = t + 1
            Cells(t, 4).Value = sLoc
            Cells(t, 5).Value = Cells(s, 1).Value
            Cells(t, 6).Value = Cells(s, 2).Value
        End If
    Next s
    Application.ScreenUpdating = True
End Sub

Hi Hans,

Thank you for your reply.

I apologise for them not matching. I think I made a mistake when copying it as an example. I have copied your code into the workbook but there have been some problems. Please see the screenshot below:

As you can see, E801 has gone into Column E when it should be in Column D and repeated for the codes within that location. 

Further down the spreadsheet, there data seems to have gone askew where it is copying the location in the product code section:

The problem I tihnk is, the spreadsheet can be "x" number of lines long, it won't ever be the same each time round if that makes sense?

Thank you

• Edited by ExcelNoob89 23 hours 41 minutes ago

could you incorporate the CurrentRegion property as long as you initially have the columns with blank data either side.

e.g.

Activecell.CurrentRegion.rows.count

Cheers

Steve

If values such as E081 and F022 end up in column E, they must be longer than 4 characters - perhaps these values contain trailing spaces?

Try

Sub CopyData()
    Dim s As Long
    Dim t As Long
    Dim m As Long
    Dim sLoc As String
    Application.ScreenUpdating = False
    m = Cells(Rows.Count, 1).End(xlUp).Row
    For s = 1 To m
        If Len(Trim(Cells(s, 1).Value)) = 4 Then
            sLoc = Trim(Cells(s, 1).Value)
        Else
            t = t + 1
            Cells(t, 4).Value = sLoc
            Cells(t, 5).Value = Cells(s, 1).Value
            Cells(t, 6).Value = Cells(s, 2).Value
        End If
    Next s
    Application.ScreenUpdating = True
End Sub

Hi Hans,

That's perfect. There were trailing whitespaces in the cell that I didn't know were there. 

Many many many thanks!