faizan haq
New Member
- Joined
- Jan 14, 2020
- Messages
- 12
- Office Version
- 2010
- 2007
- Platform
- Windows
- MacOS
Hi,
i am using below VBA code but i need to add show Subtotal value in column "K" after auto filter value "B", please advice
Sub Test()
Dim Sh As Worksheet
Dim Rng As Range
Dim c As Range
Dim List As New Collection
Dim Item As Variant
Application.ScreenUpdating = False
' *** Change Sheet name to suit ***
Set Sh = Worksheets("Sheet1")
Set Rng = Sh.Range("B13:B100" & Sh.Range("A12:A1").End(xlUp).Row)
On Error Resume Next
For Each c In Rng
List.Add c.Value, CStr(c.Value)
Next c
On Error GoTo 0
Set Rng = Sh.Range("B12:B100" & Sh.Range("A12:A1").End(xlUp).Row)
For Each Item In List
Rng.AutoFilter Field:=1, Criteria1:=Item
Sh.PrintOut
Rng.AutoFilter
Next Item
Application.ScreenUpdating = True
End Sub
i am using below VBA code but i need to add show Subtotal value in column "K" after auto filter value "B", please advice
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O |
filter value | SO# | D# | ITEMDESC | STYLECODE | ARTICLE | REGION | SIZE | C.SIZE | Total | ARTWORK | PAGES | CODE | BATCH# | |
1 | GP043697 | 41816016 | 504772 | 62759440 | S2015MGFXYB04 | FM4476 | CHINA | A128 | A128 | 58 | GP043697 | 6 | P517838B | BATCH#72281 |
2 | GP043697 | 41816016 | 504772 | 62759440 | S2015MGFXYB04 | FM4476 | CHINA | A140 | A140 | 295 | GP043697 | 6 | P517838B | BATCH#72281 |
3 | GP043697 | 41816016 | 504772 | 62759440 | S2015MGFXYB04 | FM4476 | CHINA | A152 | A152 | 792 | GP043697 | 6 | P517838B | BATCH#72281 |
4 | GP043697 | 41816016 | 504772 | 62759440 | S2015MGFXYB04 | FM4476 | CHINA | A164 | A164 | 627 | GP043697 | 6 | P517838B | BATCH#72281 |
5 | GP043697 | 41816016 | 504772 | 62759440 | S2015MGFXYB04 | FM4476 | CHINA | A176 | A176 | 268 | GP043697 | 6 | P517838B | BATCH#72281 |
6 | GP043698 | 41816016 | 504772 | 62759440 | F20FTBGFX504 | GE4647 | OTHERS | A42 | A/S | 65 | GP043698 | 6 | P517838B | BATCH#72281 |
7 | GP043698 | 41816016 | 504772 | 62759440 | F20FTBGFX504 | GE4647 | OTHERS | A46 | A/M | 123 | GP043698 | 6 | P517838B | BATCH#72281 |
8 | GP043698 | 41816016 | 504772 | 62759440 | F20FTBGFX504 | GE4647 | OTHERS | A50 | A/L | 110 | GP043698 | 6 | P517838B | BATCH#72281 |
9 | GP043698 | 41816016 | 504772 | 62759440 | F20FTBGFX504 | GE4647 | OTHERS | A54 | A/XL | 58 | GP043698 | 6 | P517838B | BATCH#72281 |
10 | GP043698 | 41816016 | 504772 | 62759440 | F20FTBGFX504 | GE4647 | OTHERS | A58 | A/2XL | 23 | GP043698 | 6 | P517838B | BATCH#72281 |
11 | GP043699 | 41816016 | 504772 | 62759440 | F20FTBGFX504 | GE4646 | OTHERS | A38 | A/XS | 4 | GP043699 | 6 | P517838B | BATCH#72281 |
12 | GP043699 | 41816016 | 504772 | 62759440 | F20FTBGFX504 | GE4646 | OTHERS | A42 | A/S | 85 | GP043699 | 6 | P517838B | BATCH#72281 |
13 | GP043699 | 41816016 | 504772 | 62759440 | F20FTBGFX504 | GE4646 | OTHERS | A46 | A/M | 152 | GP043699 | 6 | P517838B | BATCH#72281 |
14 | GP043699 | 41816016 | 504772 | 62759440 | F20FTBGFX504 | GE4646 | OTHERS | A50 | A/L | 131 | GP043699 | 6 | P517838B | BATCH#72281 |
15 | GP043699 | 41816016 | 504772 | 62759440 | F20FTBGFX504 | GE4646 | OTHERS | A54 | A/XL | 72 | GP043699 | 6 | P517838B | BATCH#72281 |
Dim Sh As Worksheet
Dim Rng As Range
Dim c As Range
Dim List As New Collection
Dim Item As Variant
Application.ScreenUpdating = False
' *** Change Sheet name to suit ***
Set Sh = Worksheets("Sheet1")
Set Rng = Sh.Range("B13:B100" & Sh.Range("A12:A1").End(xlUp).Row)
On Error Resume Next
For Each c In Rng
List.Add c.Value, CStr(c.Value)
Next c
On Error GoTo 0
Set Rng = Sh.Range("B12:B100" & Sh.Range("A12:A1").End(xlUp).Row)
For Each Item In List
Rng.AutoFilter Field:=1, Criteria1:=Item
Sh.PrintOut
Rng.AutoFilter
Next Item
Application.ScreenUpdating = True
End Sub