Sorting a List based on values from another Sheet

[aroig07]

Hi !!! I started a while ago with a big project and not knowing a lot about VBA, I am still learning and looking up new ways to do things. I am currently trying to sort a list based on 9 different variables (1 of the variables is on sheet "Daily Schedule" and the other 8 are on "CO Times"). I have the list of jobs on sheet "Daily Schedule" and a list of jobs on sheet "CO Times" (matrix). I want to be able to lookup the variables on sheet "CO Times" for those jobs that are on the list in sheet "Daily Schedule" and sort that list on the order specified (due date, paper width, code type, campos, max pages, inserts, envtype, envsize, and metro). This is the code I have up until now:

Sub CO_Calc()


'declare data range to sort
Dim TraRec As Range
Dim JobName As Range 'column B
Dim DueDate As Range 'column C

Dim PaperWidth As Range 'column D (4)
Dim CodeType As Range 'column E (5)
Dim CamPos As Range 'column F (6)
Dim MaxPages As Range 'column G (7)
Dim Inserts As Range 'column H (8)
Dim EnvType As Range 'column I (9)
Dim EnvSize As Range 'column J (10)
Dim Metro As Range 'column K (11)


Set DaySch = Sheets("Daily Schedule")
Set COTimes = Sheets("CO Times")
Set TraRec = COTimes.Range("A3:P200")
Set JobName =DaySch.Range("B1")
Set DueDate = DaySch.Range("C1")
Set PaperWidth = COTimes.Range("D1")
Set CodeType = COTimes.Range("E1")
Set CamPos = COTimes.Range("F1")
Set MaxPages = COTimes.Range("G1")
Set Inserts = COTimes.Range("H1")
Set EnvType =COTimes.Range("I1")
Set EnvSize = COTimes.Range("J1")
Set Metro = COTimes.Range("K1")


JobName.Sort Key1:=PaperWidth, Order1:=xlAscending, Header:=xlYes






End Sub

 

[BlueHornet]

Hello.

I don't understand enough about all that you're trying to accomplish here -- and I have a sense that maybe you don't yet, either. So let's start by stages, and the last stage will be the code (at least for me), until it's clear that the process itself is completely worked out.

So, it seems that you're trying to pull data from the master "CO Times" worksheet into the "Daily Schedule" sheet, and then sort at Daily Schedule based on whatever hierarchy of sorting matters most to you. Is that correct so far?

Assuming that it is, your "Daily Schedule" sheet will have some list of values that will correspond to unique values in "CO Times", and that CO Times also has all of the other data that you need or want to pull into Daily Schedule. In that case, let's say that Daily Schedule has a short list of coded jobs (or whatever) that I'll call
A123
A124
A125
and your first job is to capture the relevant data from CO Times to match those codes. This formula should work okay, given allowances for differences in your data layout:
If the codes above are in column A:A, starting at A2, then at B2 enter:

=VLOOKUP( $A2, Sheet4!$A$2:$J$4, COLUMNS( $A:B), FALSE)

copy that from B2 through the other cells in the table you're creating on Daily Schedule - the formula is written that way so that it will automatically pull from the matching columns in CO Times.

Once you have your table built, and assuming you want to sort this on multiple values (a somewhat silly task for a three-row table, but the principle can be illustrated anyway), what you'll want to do is to manually perform ALL of the sorts that you want, but starting from the LEAST significant column, ending with the final sort on the MOST significant column. (I'm not sure if VBA can perform a single sort on multiple values; it's not a thing that I have ever tried before. Maybe later.) Each sort must be of the full table on Daily Schedule, including the lookup codes in A:A.

If we're okay so far, then it might be time to start thinking about how to do some of this via VBA, but the process should be worked out manually to some point just to prove that we both know what you're doing.