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

Veni11

New Member
Joined
Oct 20, 2023
Messages
22
Office Version
  1. 365
Platform
  1. Windows
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
 
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.
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!
 
Upvote 0

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
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.
 
Upvote 1
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.

VBA Code:
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
 
Upvote 0
If you start a new thread for this issue you will receive much more suggestions and you can benefit more.
 
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,324
Members
452,635
Latest member
laura12345

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top