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

 

[Veni11]

Actually the code does what I understand. Sorry, maybe I am not understanding what you mean. You may wait for other answers.
Check the sample file. Material (2) sheet sorted like Master.

WeTransfer - Send Large Files & Share Photos Online - Up to 2GB Free

WeTransfer is the simplest way to send your files around the world

we.tl

I have a question, is it possible to use the sheet Master only as a Custom Order List? Or is your code exactly doing that?
Because i tried the code again now and it worked but 90% of the items jumped down to the Row(5844) and were sorted there perfectly fine but a few of the items are at Row(3). I don't know what the problem is there but if it would stand at the Row(3) as I need and want it than it would be everything perfectly fine!

I would like to apologize for taking so much time from you. Thank you for your help!

 

[Legacy 143009]

I have a question, is it possible to use the sheet Master only as a Custom Order List? Or is your code exactly doing that?
Because i tried the code again now and it worked but 90% of the items jumped down to the Row(5844) and were sorted there perfectly fine but a few of the items are at Row(3). I don't know what the problem is there but if it would stand at the Row(3) as I need and want it than it would be everything perfectly fine!

I would like to apologize for taking so much time from you. Thank you for your help!

The problem can be several things:
1. Start your items from row 3. I saw 3rd row as empty on both Master and Material sheets.
2. You can't have any empty rows, especially in Master sheet.
3. Master sheet must contain all the items. First, complete Master sheet and try again.

Maybe in row 5844 it is skipping an empty row or non-existing item. I don't know.

 

[Veni11]

The problem can be several things:
1. Start your items from row 3. I saw 3rd row as empty on both Master and Material sheets.
2. You can't have any empty rows, especially in Master sheet.
3. Master sheet must contain all the items. First, complete Master sheet and try again.

Maybe in row 5844 it is skipping an empty row or non-existing item. I don't know.

It works now way better thank you very much!

The problem with the empty rows in the sheet Material come from the fact that I copy parts from other sheets with offset. Is there another way to copy this to the last line dynamically? Because i have several codes where i copy items if a specific item is already in the sheet material. But my solution till now is to do it with offset which is not the best solution anyway but I couldn't figure it out to implent it for a dynamic range. Because always when it copies some new items the range is new.

Private Sub Wall()

Dim i As Long, lastrow1 As Long
Dim myname As String

lastrow1 = Sheets("Material").Range("K" & Rows.Count).End(xlUp).Row

For i = 2 To lastrow1
myname = "33527"

Application.ScreenUpdating = False


    If Worksheets("Material").Cells(i, "K").Value = myname Then
    Worksheets("stock").Activate
    Worksheets("stock").Rows("22:25").Copy
    Worksheets("Material").Activate
    Sheets("Material").Range("A" & Rows.Count).End(xlUp).Offset(27, 0).PasteSpecial xlPasteValues
    End If

Application.CutCopyMode = False
Next i

Application.ScreenUpdating = True


End Sub

 

[Legacy 143009]

If you start a new thread for this issue you will receive much more suggestions and you can benefit more.

 

[Veni11]

If you start a new thread for this issue you will receive much more suggestions and you can benefit more.

Alright. Thank you so much for your help!

 

[Legacy 143009]

You are welcome