VBA Code that uses CustomOrder to Sort in other Worksheet in the same Workbook

[Veni11]

Hii
I have a difficult issue for me as I'm still a bit new to VBA coding.
Here's what I'm trying to solve. I have an Excel template with different sheets. One of these sheets is called "MASTER" and it contains all the article numbers that are available and they are entered in a defined order. In the "Material" sheet there are various item numbers that are randomly distributed in a column, but these item numbers are all contained in the "MASTER" sheet. My goal is to sort the "Material" sheet based on the order in the "MASTER". The code should be structured in such a way that it doesn't matter which article numbers appear in the "Material" sheet, they are then sorted in the column as they are in the "Master". The range is C3:C4000 in the "MASTER" sheet.
In the end, the structure would be the same for each new "Material" sheet, even if not all item numbers are included.

I hope I described it understandable and that someone has an idea :D

 

[Legacy 143009]

Hi, two questions:

1. Where (in which column) are the item numbers placed in "Material" sheets?
2. How to differentiate Material sheets? Do you use a distinctive naming scheme?

 

[Veni11]

Hi, two questions:

1. Where (in which column) are the item numbers placed in "Material" sheets?
2. How to differentiate Material sheets? Do you use a distinctive naming scheme?

Hii

1. the item numbers are placed in the column C, further information such as length, width and height is entered in other columns.
2. Yes I'm using a distinctive naming scheme. To perhaps explain it in more detail. The sheets always remain the same, including the name, the only thing that changes are the workbook names and thus the number and different item numbers in the "Material" sheet.

I hope that makes sense to you. I'm sorry because english is not my native language. Thank you for your response!

 

[Legacy 143009]

2. Yes I'm using a distinctive naming scheme. To perhaps explain it in more detail. The sheets always remain the same, including the name, the only thing that changes are the workbook names and thus the number and different item numbers in the "Material" sheet.

I mean do you have distinctive names for material sheets? I mean your tab names look like:
MASTER, Material1, Material2, Material3, Report, RawData..... etc..

I mean is it possible to differentiate them from the other sheets?
I said too many I means

 

[Veni11]

I mean do you have distinctive names for material sheets? I mean your tab names look like:
MASTER, Material1, Material2, Material3, Report, RawData..... etc..

I mean is it possible to differentiate them from the other sheets?
I said too many I means

Oh yes the tab names look like this:
Material, Temp, Stock, Special sizes, MASTER

So basically the sheets "Temp, Stock, Special sizes" are just filled with some items that you can copy the row and paste it to the sheet "Material" if you need them.

For example the "material" sheet contains the following data:

Column A(number of pieces) Column B(additional) Column C(Item Number)
4 0 S1885-500
7 3 S1885-300
2 2 S1885-2000
5 1 S1885-150
3 3 S2555-750

In the sheet "MASTER" there are plenty more of this item Numbers as like:
S2555-550
S2555-600
S2555-750
S2555-800
S1885-100
S1885-150
S1885-200
S1885-300

and so on. So that in the example above the final screen in the sheet "Material" should look like this after the sortation:
3 3 S2555-750
5 1 S1885-150
7 3 S1885-300
4 0 S1885-500
2 2 S1885-2000

 

[Legacy 143009]

Aahhh.. I thought you have more than 1 Materials sheet. Then this should work OK:

Sub test()
  Dim masterArray As Variant, materialArray As Variant, tempArray As Variant
  Dim masterNumbers As Object, a As Object
  Dim i As Long, j As Long, k As Long, c As Long
  Set masterNumbers = CreateObject("Scripting.Dictionary")
  Set a = Application
 
  With Worksheets("MASTER")
  masterArray = a.Transpose(Intersect(.UsedRange, .Columns("C")))
  End With
 
  For i = 2 To UBound(masterArray)
    If Not masterNumbers.Exists(masterArray(i)) Then
      c = c + 1
      masterNumbers.Add masterArray(i), c
    End If
  Next

  With Worksheets("Material")
  materialArray = .UsedRange
  For i = 2 To UBound(materialArray, 1) - 1
    For j = i + 1 To UBound(materialArray, 1)
      If masterNumbers(materialArray(j, 3)) < masterNumbers(materialArray(i, 3)) Then
        tempArray = a.Index(materialArray, i, 0)
        For k = 1 To UBound(materialArray, 2)
          materialArray(i, k) = materialArray(j, k)
          materialArray(j, k) = tempArray(k)
        Next
      End If
    Next
  Next

  .UsedRange.Value = materialArray
End Sub

 

[Veni11]

Aahhh.. I thought you have more than 1 Materials sheet. Then this should work OK:

Sub test()
  Dim masterArray As Variant, materialArray As Variant, tempArray As Variant
  Dim masterNumbers As Object, a As Object
  Dim i As Long, j As Long, k As Long, c As Long
  Set masterNumbers = CreateObject("Scripting.Dictionary")
  Set a = Application
 
  With Worksheets("MASTER")
  masterArray = a.Transpose(Intersect(.UsedRange, .Columns("C")))
  End With
 
  For i = 2 To UBound(masterArray)
    If Not masterNumbers.Exists(masterArray(i)) Then
      c = c + 1
      masterNumbers.Add masterArray(i), c
    End If
  Next

  With Worksheets("Material")
  materialArray = .UsedRange
  For i = 2 To UBound(materialArray, 1) - 1
    For j = i + 1 To UBound(materialArray, 1)
      If masterNumbers(materialArray(j, 3)) < masterNumbers(materialArray(i, 3)) Then
        tempArray = a.Index(materialArray, i, 0)
        For k = 1 To UBound(materialArray, 2)
          materialArray(i, k) = materialArray(j, k)
          materialArray(j, k) = tempArray(k)
        Next
      End If
    Next
  Next

  .UsedRange.Value = materialArray
End Sub

Do I have to pay attention to anything else if I have headers? Or does the code also work with this, since the article numbers only start from C3 and the title is in C1 & C2. I'll try it in a few minutes.

 

[Legacy 143009]

Then start only i indexes from 3. This should be enough.

 

[Veni11]

Then start only i indexes from 3. This should be enough.

Excel has hung up and is still loading "no respond" I will give you feedback on whether it worked as soon as it is finished but anyway thank you very much for your support!

 

[Legacy 143009]

How many rows do you have?