Converting multiple spread sheets in to 1 sheet

Richard,

I can't quite understand what you are trying to do here. Can you run through an example with a specific product so I can see what you are trying to do? When I look at the product codes on the pick sheet they do not directly relate to a code on the master sheet. Are you wanting the 30 pick sheets to be combined into the All Codes Master format?

If I can understand I can help.

Regards

David

Richard,

We should be able to derive the master code. It seems to me it is as follows:

Code first (e.g. LUCHZ)
first to numbers in the description field
colour code
size

In the attached you will see I have an excel formula to derive the master code, please check if this is correct.

If so I should not be difficult to write a macro to open 30 files and build the master code sheet.

Regards

David

Richard,

Different length codes are not an issue. See attached 3 files, do the following

1) download to a temporary directory
2) open All Codes Master, note quantities are zero
3) open ColourCodesTblWB, note lookup table
4) open Pick Sheet Sample 1, run the macro. This transfers all the quantities to the all codes master

The algorithm is as follows:

1) loop through the pick sheet quantities in range E14:J27
2) if a quantity is > 0 derive the product code
3) loop through product codes in All Codes Master workbook, when it matches write the quantity.

Code is below.

Sub UpdateAllCodesMaster()

    Dim OrderQtyRg, OrderQtyCell As Range
    Dim UPC As String
    Dim OutputRow As Integer
    Dim PickSheetWB As String
    
    Set OrderQtyRg = Range("E14:J24")
    PickSheetWB = ActiveWorkbook.Name
    
    'Loop through each cell where an order quantity would be
    For Each OrderQtyCell In OrderQtyRg
        If OrderQtyCell.Value > 0 Then
            UPC = CompileUPC(OrderQtyCell)
            Workbooks("All Codes Master.xls").Activate
            'Loop through master codes until a digit is found
            OutputRow = 2
            Do Until Cells(OutputRow, 2).Value = UPC
                OutputRow = OutputRow + 1
            Loop
            'Output quantity
            Cells(OutputRow, 6).Value = OrderQtyCell.Value
            Workbooks(PickSheetWB).Activate
        End If
    Next OrderQtyCell

End Sub
Function CompileUPC(OrderQtyCell As Range) As String

    Dim Code, Num, Colour, Size, tempstring As String
    Dim ColourCodeTblRg As Range
    Dim MyWB As String
       
    Code = Cells(OrderQtyCell.Row, 2)
    Colour = Cells(OrderQtyCell.Row, 4)
    Colour = Application.WorksheetFunction.VLookup(Colour, Workbooks("ColourCodeTblWB.xlsx").Worksheets("ColourCodeTblWS").Range("A2:B7"), 2, False)
    Size = Cells(11, OrderQtyCell.Column)
    Num = ExtractNumFromDesc(Cells(OrderQtyCell.Row, 3).Value)
    CompileUPC = Code & Num & Colour & Size

End Function

Function ExtractNumFromDesc(ItemDesc As String) As String

    Dim x As Integer
    
    x = 1
    ExtractNumFromDesc = ""
    
    'Loop until a digit is found
    Do Until Asc(Mid(ItemDesc, x, 1)) >= 49 And Asc(Mid(ItemDesc, x, 1)) <= 57
        x = x + 1
    Loop
    'Keep looping and build string until there is no longer a number
    Do Until Asc(Mid(ItemDesc, x, 1)) < 49 Or Asc(Mid(ItemDesc, x, 1)) > 57
        ExtractNumFromDesc = ExtractNumFromDesc & Mid(ItemDesc, x, 1)
        x = x + 1
    Loop

End Function

The only other thing that needs doing is to add an extra loop to run through all your pick sheets. Let me know what you think and lets enhance from there.

Regards

David