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]

Paste in your data and i'll have a look. (Not a screenshot)

 

[santa12345]

Hi. will this work?
I put in a few blanks in category #3.
Thank you !!

Main Part #	Qty Requested	Cost	Ext Cost	#2 - Top	#3 - Bottom	#4 - Seal
10-23C-ABC-v1.9	3​		0​	23C	ABC	v1.9
10-23C-ABC-v1.10	4​		0​	23C	ABC	v1.10
10-23C-ABC-v1.11	5​		0​	23C	ABC	v1.11
10-23C-ABC-v1.12	3​		0​	23C	ABC	v1.12
10-23C-ABC-v1.13	2​		0​	23C	ABC	v1.13
10-23C-ABC-v1.14	21​		0​	23C	ABC	v1.14
10-23C-ABC-v1.15	7​		0​	23C	ABC	v1.15
10-23C-ABC-v1.9	6​		0​	23C	ABC	v1.9
10-23C-ABC-v1.10	3​		0​	23C	ABC	v1.10
10-23C-ABC-v1.11	5​		0​	22C		v1.11
10-23C-ABC-v1.12	4​		0​	24C	ABC	v1.12
10-23C-ABC-v1.20	3​		0​	23C	ABC	v1.20
10-23C-ABC-v1.21	2​		0​	23C	ABC	v1.21
10-23C-ABC-v1.9	1​		0​	23C	ABC	v1.9
10-23C-ABC-v1.10	3​		0​	23C	DEF	v1.10
10-23C-ABC-v1.11	4​		0​	23C	DEF	v1.11
10-23C-ZZZ-v2.0	100​		0​	23C	ZZZ	v2.0
10-23C-ABC-v1.12	5​		0​	23C	DEF	v1.12
10-23C-ABC-v1.20	3​		0​	23C		v1.20
10-23C-ABC-v1.21	2​		0​	23C	DEF	v1.21
10-23C-ABC-v1.9	21​		0​	23C	DEF	v1.9
10-23C-ABC-v1.10	7​		0​	23C	DEF	v1.10
10-23C-ABC-v1.11	6​		0​	23C	DEF	v1.11
10-23C-ABC-v1.12	3​		0​	23C		v1.12
10-23C-ABC-v1.20	5​		0​	23C	ABC	v1.20
10-23C-ABC-v1.21	4​		0​	23C		v1.21
10-23C-ABC-v1.9	3​		0​	23C	ABC	v1.9
10-23D-GGG-v2.4	50​		0​	23D	GGG	v2.4
20-24D-GGG-v2.4	50​		20​	24D	GGG	v2.4
69-11Z-DDD-v1.0	1000​		69​	11Z	DDD	v1.0

 

[mrshl9898]

Sub CreateSummary()

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

Sheets.Add.Name = "Summary"

Set sht = Sheets("Order")
Set sht2 = Sheets("Summary")

sht2.Range("A1") = "Sub Part"
sht2.Range("B1") = "Total Qty"
Lastrow = sht.Cells(sht.Rows.Count, "D").End(xlUp).Row
sht.Range("D2:D" & Lastrow).Copy sht2.Range("A2")
Lastrow2 = sht2.Cells(sht2.Rows.Count, "A").End(xlUp).Row
Lastrow = sht.Cells(sht.Rows.Count, "E").End(xlUp).Row
sht.Range("E2:E" & Lastrow).Copy sht2.Range("A" & Lastrow2 + 1)
Lastrow2 = sht2.Cells(sht2.Rows.Count, "A").End(xlUp).Row
Lastrow = sht.Cells(sht.Rows.Count, "F").End(xlUp).Row
sht.Range("F2:F" & Lastrow).Copy sht2.Range("A" & Lastrow2 + 1)
Lastrow2 = sht2.Cells(sht2.Rows.Count, "A").End(xlUp).Row
Lastrow = sht.Cells(sht.Rows.Count, "G").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:=sht2.Range("A1"), Order1:=xlAscending, Header:=xlYes


rownum = 2

Do Until sht2.Cells(rownum, 2) = ""
    If sht2.Cells(rownum, 1) = "" Then
        Rows(rownum).Delete
    End If
rownum = rownum + 1
Loop


rownum = 2

Do Until sht2.Cells(rownum, 1) = ""
    If sht2.Cells(rownum, 1) Like "v*" Then
        Rows(rownum).Insert
        Lastrow2 = sht2.Cells(sht2.Rows.Count, "A").End(xlUp).Row
        Rows(Lastrow2).Copy Rows(rownum)
        Rows(Lastrow2).ClearContents
        Exit Sub
    End If
rownum = rownum + 1
Loop

End Sub

 

[santa12345]

Thanks...I'm getting a type mismatch on this line of code.

 

[mrshl9898]

I can't seem to replicate that issue... Hopefully someone can help