Resize data to new sheet, copying data N times based count total from specific columns containing data

  • Hi

    I have a list of data in table format which is copied to 2 different tabs.

    Labels Tab - replicates the data N times based on the Value in each column H:M, then based on the size details in column F, fills in the size on each row. - This Macro works perfectly well.


    Export Tab - counts the number of cells containing data in each row, and replicates N times based on the Count. (if there are 3 sizes with orders, copy the row 3 times and show which sizes are ordered) This replication is working but I cannot figure out how to get the size to show only once and not for the count. The code is using .value which is causing the issue but I cannot figure out. after several hours of trying, what to change it to :|

    I hope this makes sense.


    Many thanks in advance.


    J:)


  • Hello,


    Looks like you need to determine if the first character of your Size Options is a Letter or a Number ...


    Code
    1. Sub TestSizeOptions()
    2. If Asc(Left(ActiveCell.Value, 1)) < 65 Then
    3. MsgBox "Number"
    4. Else
    5. MsgBox "Letter"
    6. End If
    7. End Sub


    Hope this will help

    :)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Hi Carim,

    Thanks for the above. I think line 28 does check for numeric/alpha and it produce the correct result in the label macro, but I'm stuck with the size replicating per the count instead of just once per line in the Export macro. Please let me know if I am not explaining clearly.


    Code
    1. If (IsNumeric(Left(ws1.Cells(i, 6).Value, 1))) = True Then


    Cheers

    J

  • Hello Jan,


    Your explanation is very clear ... !!!:)


    Only I dashed too quickly to your macro and thought the problem was related to the test ... which is not ...


    Will take a look at your file ...;)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Hello Jan,


    Attached is your test file with the requested modification for the Export macro ...


    Hope this will help

    :)

    Files

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Hi Carim,

    Thank you for the file ..works perfectly. I figured it probably needed to do a check of the cell, but converting the mental logic to VBA is sometimes difficult especially when one is a beginner. I enjoy the challenge and am very grateful for the help and your dedication to helping others.

    Cheers and have a great day.

    J:)

  • Hello Jan,


    First, let me congratulate you for your " cerebral gymnastic " ability, and your proficiency at using Offset and Resize ... which is not simple at all for a beginner ...:)


    Second, let me thank you ... for your Thanks and for the Like :thumbup:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Hello Jan,


    Granted ... the internet is such a great resource ...


    but my compliments were more precisely targeting your perseverance ;)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)