Excel VBA - How To Sort and Total Data in a Summary Tab

[santa12345]

Hello.
I am trying to do the following.
Lets say I have a order tab.
Main Part # is listed in column A. Qty is next. The Main Part # consists of 4 parts which is columns D - G.
I have the mid and left functions working to populate D-G... and the below screen shot.

What I want to create...is a macro to do the following.
1. Create a summary tab.
2. Get the unique sub parts and total up the qty for each
3. The summary tab would be in the tube,top,bottom,seal order if possible.
4. Total up the qtys per sub part.

Here is the final output I am looking for on the summary tab.

Please let me know if you have any questions.
Any inputs and/or suggestions would be greatly appreciated.

 

[mrshl9898]

I've called the Order tab "Order".

Sub CreateSummary()

Dim sht As Worksheet
Dim Lastrow As Long
Dim sht2 As Worksheet
Dim Lastrow2 As Long

Sheets.Add.Name = "Summary"

Set sht = Sheets("Order")
Lastrow = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row
Set sht2 = Sheets("Summary")

sht2.Range("A1") ="Sub Part"
sht2.Range("B1") ="Total Qty"
sht.Range("D2:D" & Lastrow).Copy sht2.Range("A2")
Lastrow2 = sht2.Cells(sht2.Rows.Count, "A").End(xlUp).Row
sht.Range("E2:E" & Lastrow).Copy sht2.Range("A" & Lastrow2 + 1)
Lastrow2 = sht2.Cells(sht2.Rows.Count, "A").End(xlUp).Row
sht.Range("F2:F" & Lastrow).Copy sht2.Range("A" & Lastrow2 + 1)
Lastrow2 = sht2.Cells(sht2.Rows.Count, "A").End(xlUp).Row
sht.Range("G2:G" & Lastrow).Copy sht2.Range("A" & Lastrow2 + 1)
Lastrow2 = sht2.Cells(sht2.Rows.Count, "A").End(xlUp).Row

sht2.Range("A1:A" & Lastrow2).RemoveDuplicates Columns:=Array(1), Header:=xlYes
sht2.Range("B2").FormulaR1C1 = _
    "=SUMIFS(Order!C,Order!C[2],RC[-1])+SUMIFS(Order!C,Order!C[3],RC[-1])+SUMIFS(Order!C,Order!C[4],RC[-1])+SUMIFS(Order!C,Order!C[5],RC[-1])"
Lastrow2 = sht2.Cells(sht2.Rows.Count, "A").End(xlUp).Row
sht2.Range("B2").Copy sht2.Range("B2:B" & Lastrow2)

sht2.Range("A1:B" & Lastrow2).Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlYes


End Sub

 

[santa12345]

This is fantastic ! Thank you!!!!
I assume by using the .end(x1up).row code... one line item or 1000 line items...will not matter . it will pick up everything.
I may have to have some calcs to the summary tab and copy the formula down for all populated cells in columns a&b. if that make sense.

 

[santa12345]

Something like this...

Range("D2").Select
ActiveCell.FormulaR1C1 = "=RC[-2]*RC[-1]"
Range("D2").Select

What would be the solution for the below...instead of using static cells... as the # of rows will be different from order to order?

Selection.AutoFill Destination:=Range("D2:D14")
Range("D2:D14").Select

 

[santa12345]

Sorry one more note:
When I add a main part...screen shot attached

Summary tab shows:

Which is correct... but the sub part ZZZ is now out of order
sub part 10 - is sub part #1
sub part 23C is sub part #2
sub part ABC, DEF, ZZZ are sub parts #3
and all the v1.** are sub parts #4

I need all the sub parts sorted per group (#1) then #2 , #3, #4....

Output (summary tab) needs to be ... if possible..like this

Thank you again !!!!

 

[mrshl9898]

This is fantastic ! Thank you!!!!
I assume by using the .end(x1up).row code... one line item or 1000 line items...will not matter . it will pick up everything.
I may have to have some calcs to the summary tab and copy the formula down for all populated cells in columns a&b. if that make sense.

Correct, here you can see the formula added, last row calculated and formula copied down.

sht2.Range("B2").FormulaR1C1 = _
    "=SUMIFS(Order!C,Order!C[2],RC[-1])+SUMIFS(Order!C,Order!C[3],RC[-1])+SUMIFS(Order!C,Order!C[4],RC[-1])+SUMIFS(Order!C,Order!C[5],RC[-1])"
Lastrow2 = sht2.Cells(sht2.Rows.Count, "A").End(xlUp).Row
sht2.Range("B2").Copy sht2.Range("B2:B" & Lastrow2)

 

[mrshl9898]

Something like this...

Range("D2").Select
ActiveCell.FormulaR1C1 = "=RC[-2]*RC[-1]"
Range("D2").Select

What would be the solution for the below...instead of using static cells... as the # of rows will be different from order to order?

Selection.AutoFill Destination:=Range("D2:D14")
Range("D2:D14").Select

Since the lastrow is already determined, use:

sht2.Range("D2").FormulaR1C1 = "=RC[-2]*RC[-1]"
sht2.Range("D2").Copy sht2.Range("D2:D" & Lastrow2)

 

[mrshl9898]

Sorry one more note:
When I add a main part...screen shot attached

View attachment 38249

Summary tab shows:

View attachment 38250

Which is correct... but the sub part ZZZ is now out of order
sub part 10 - is sub part #1
sub part 23C is sub part #2
sub part ABC, DEF, ZZZ are sub parts #3
and all the v1.** are sub parts #4

I need all the sub parts sorted per group (#1) then #2 , #3, #4....

Output (summary tab) needs to be ... if possible..like this

View attachment 38251

Thank you again !!!!

Sorry, change the sort to B1 and Descending

sht2.Range("A1:B" & Lastrow2).Sort Key1:=Range("B1"), Order1:=xlDescending, Header:=xlYes

That'll fix the ZZZ issue. As for getting v2's after v1's..... It doesn't look like any Sort will do that.

You'll need to find a way to change the names that you can then sort by.

 

[santa12345]

Thank you for the replies.
When I change the last line of code
from
sht2.Range("A1:B" & Lastrow2).Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlYes
to
sht2.Range("A1:B" & Lastrow2).Sort Key1:=Range("B1"), Order1:=xlDescending, Header:=xlYes
I get this.

This was much closer...except for the ZZZ issue..

 

[mrshl9898]

You are right, I didn't read the original post correctly.

Is the only issue ZZZ? will there ever be anything else starting with W, X, Y or Z?