Want to generate tables from column of data thru VBA.

[AGU]

Hi guys, good day to all. Hope you all are ok. Guys I would like to generate a sales report from column of data. Normally I use a pivot table, but I need to automate this by using VBA. Basically, there's a new entry for every time someone uses a sales report. What I'd want is VBA creating three tables using data from data sheet similar to the functionality of a pivot table. I have tried some code, I'm trying to use array formula, but I am unable to take out unique values. Here is a code I have so far & additional help will be greatly appreciated.

Sub test10()

Dim Arr As Variant
Arr = Sheets("SalesOrders").Cells(1).CurrentRegion

Dim i As Long
For i = LBound(Arr, 1) + 1 To UBound(Arr, 1)
Next i

Sheets("sheet1").Range("C4").CurrentRegion.ClearContents

Dim rowcount As Long, columncount As Long
rowcount = UBound(Arr, 1)
columncount = UBound(Arr, 2)

Sheets("sheet1").Range("c4").Resize(rowcount, columncount).Value = Arr

End Sub

Sales Orders Summary
Region	S#	Rep	Total	Total%	Unit Cost	Unit Cost%
Central	1	Andrews	438	2%	10	1%
	2	Gill	1,750	9%	41	5%
	3	Jardine	2,812	14%	40	5%
	4	Kivell	3,109	16%	174	20%
	5	Morgan	1,388	7%	26	3%
	6	Smith	1,641	8%	141	16%
East	7	Howard	537	3%	7	1%
	8	Jones	2,363	12%	56	6%
	9	Parent	3,102	16%	56	6%
West	10	Sorvino	1,284	7%	300	34%
	11	Thompson	1,203	6%	22	3%
Total			19,628	100%	873	100%
Region	Total	Total%	Unit Cost	Unit Cost%
Central	11,139	57%	432	50%
East	6,002	31%	119	14%
West	2,487	13%	322	37%
Total	19,628	100%	873	100%
Item	Total	Total%	Unit Cost	Unit Cost%
Binder	9,578	49%	173	20%
Desk	1,700	9%	525	60%
Pen	2,045	10%	56	6%
Pen Set	4,170	21%	83	10%
Pencil	2,135	11%	36	4%
Total	19,628	100%	873	100%

Sales Report Jan 2022 (Project).xls
	A	B	C	D	E	F
1	Region	Rep	Item	Units	Unit Cost	Total
2	Central	Smith	Desk	2	125.00	250.00
3	Central	Kivell	Desk	5	125.00	625.00
4	Central	Gill	Pencil	7	1.29	9.03
5	Central	Jardine	Binder	11	4.99	54.89
6	Central	Andrews	Pencil	14	1.29	18.06
7	Central	Gill	Pen	27	19.99	539.73
8	Central	Morgan	Binder	28	8.99	251.72
9	Central	Andrews	Binder	28	4.99	139.72
10	Central	Jardine	Pencil	36	4.99	179.64
11	Central	Kivell	Pen Set	42	23.95	1,005.90
12	Central	Gill	Binder	46	8.99	413.54
13	Central	Kivell	Binder	50	19.99	999.50
14	Central	Jardine	Pen Set	50	4.99	249.50
15	Central	Gill	Pencil	53	1.29	68.37
16	Central	Morgan	Pen Set	55	12.49	686.95
17	Central	Andrews	Pencil	66	1.99	131.34
18	Central	Smith	Pencil	67	1.29	86.43
19	Central	Andrews	Pencil	75	1.99	149.25
20	Central	Gill	Binder	80	8.99	719.20
21	Central	Smith	Binder	87	15.00	1,305.00
22	Central	Jardine	Pencil	90	4.99	449.10
23	Central	Morgan	Pencil	90	4.99	449.10
24	Central	Jardine	Binder	94	19.99	1,879.06
25	Central	Kivell	Pen Set	96	4.99	479.04
26	East	Jones	Binder	4	4.99	19.96
27	East	Parent	Pen	15	19.99	299.85
28	East	Jones	Pen Set	16	15.99	255.84
29	East	Howard	Binder	29	1.99	57.71
30	East	Jones	Pencil	35	4.99	174.65
31	East	Jones	Binder	60	4.99	299.40
32	East	Jones	Binder	60	8.99	539.40
33	East	Jones	Pen Set	62	4.99	309.38
34	East	Jones	Pen	64	8.99	575.36
35	East	Parent	Pen Set	74	15.99	1,183.26
36	East	Parent	Binder	81	19.99	1,619.19
37	East	Jones	Pencil	95	1.99	189.05
38	East	Howard	Pen	96	4.99	479.04
39	West	Sorvino	Desk	3	275.00	825.00
40	West	Sorvino	Binder	7	19.99	139.93
41	West	Thompson	Pencil	32	1.99	63.68
42	West	Sorvino	Pencil	56	2.99	167.44
43	West	Thompson	Binder	57	19.99	1,139.43
44	West	Sorvino	Pen	76	1.99	151.24
SalesOrders

Cell Formulas
Range	Formula
F2:F44	F2	=E2*D2

 

[AGU]

Hi guys, good day to all. Hope you all are ok. Guys I would like to generate a sales report from column of data. Normally I use a pivot table, but I need to automate this by using VBA. Basically, there's a new entry for every time someone uses a sales report. What I'd want is VBA creating three tables using data from data sheet similar to the functionality of a pivot table. I have tried some code, I'm trying to use array formula, but I am unable to take out unique values. Here is a code I have so far & additional help will be greatly appreciated.

Sub test10()

Dim Arr As Variant
Arr = Sheets("SalesOrders").Cells(1).CurrentRegion

Dim i As Long
For i = LBound(Arr, 1) + 1 To UBound(Arr, 1)
Next i

Sheets("sheet1").Range("C4").CurrentRegion.ClearContents

Dim rowcount As Long, columncount As Long
rowcount = UBound(Arr, 1)
columncount = UBound(Arr, 2)

Sheets("sheet1").Range("c4").Resize(rowcount, columncount).Value = Arr

End Sub

Sales Orders Summary
Region	S#	Rep	Total	Total%	Unit Cost	Unit Cost%
Central	1	Andrews	438	2%	10	1%
	2	Gill	1,750	9%	41	5%
	3	Jardine	2,812	14%	40	5%
	4	Kivell	3,109	16%	174	20%
	5	Morgan	1,388	7%	26	3%
	6	Smith	1,641	8%	141	16%
East	7	Howard	537	3%	7	1%
	8	Jones	2,363	12%	56	6%
	9	Parent	3,102	16%	56	6%
West	10	Sorvino	1,284	7%	300	34%
	11	Thompson	1,203	6%	22	3%
Total			19,628	100%	873	100%
Region	Total	Total%	Unit Cost	Unit Cost%
Central	11,139	57%	432	50%
East	6,002	31%	119	14%
West	2,487	13%	322	37%
Total	19,628	100%	873	100%
Item	Total	Total%	Unit Cost	Unit Cost%
Binder	9,578	49%	173	20%
Desk	1,700	9%	525	60%
Pen	2,045	10%	56	6%
Pen Set	4,170	21%	83	10%
Pencil	2,135	11%	36	4%
Total	19,628	100%	873	100%

Sales Report Jan 2022 (Project).xls
	A	B	C	D	E	F
1	Region	Rep	Item	Units	Unit Cost	Total
2	Central	Smith	Desk	2	125.00	250.00
3	Central	Kivell	Desk	5	125.00	625.00
4	Central	Gill	Pencil	7	1.29	9.03
5	Central	Jardine	Binder	11	4.99	54.89
6	Central	Andrews	Pencil	14	1.29	18.06
7	Central	Gill	Pen	27	19.99	539.73
8	Central	Morgan	Binder	28	8.99	251.72
9	Central	Andrews	Binder	28	4.99	139.72
10	Central	Jardine	Pencil	36	4.99	179.64
11	Central	Kivell	Pen Set	42	23.95	1,005.90
12	Central	Gill	Binder	46	8.99	413.54
13	Central	Kivell	Binder	50	19.99	999.50
14	Central	Jardine	Pen Set	50	4.99	249.50
15	Central	Gill	Pencil	53	1.29	68.37
16	Central	Morgan	Pen Set	55	12.49	686.95
17	Central	Andrews	Pencil	66	1.99	131.34
18	Central	Smith	Pencil	67	1.29	86.43
19	Central	Andrews	Pencil	75	1.99	149.25
20	Central	Gill	Binder	80	8.99	719.20
21	Central	Smith	Binder	87	15.00	1,305.00
22	Central	Jardine	Pencil	90	4.99	449.10
23	Central	Morgan	Pencil	90	4.99	449.10
24	Central	Jardine	Binder	94	19.99	1,879.06
25	Central	Kivell	Pen Set	96	4.99	479.04
26	East	Jones	Binder	4	4.99	19.96
27	East	Parent	Pen	15	19.99	299.85
28	East	Jones	Pen Set	16	15.99	255.84
29	East	Howard	Binder	29	1.99	57.71
30	East	Jones	Pencil	35	4.99	174.65
31	East	Jones	Binder	60	4.99	299.40
32	East	Jones	Binder	60	8.99	539.40
33	East	Jones	Pen Set	62	4.99	309.38
34	East	Jones	Pen	64	8.99	575.36
35	East	Parent	Pen Set	74	15.99	1,183.26
36	East	Parent	Binder	81	19.99	1,619.19
37	East	Jones	Pencil	95	1.99	189.05
38	East	Howard	Pen	96	4.99	479.04
39	West	Sorvino	Desk	3	275.00	825.00
40	West	Sorvino	Binder	7	19.99	139.93
41	West	Thompson	Pencil	32	1.99	63.68
42	West	Sorvino	Pencil	56	2.99	167.44
43	West	Thompson	Binder	57	19.99	1,139.43
44	West	Sorvino	Pen	76	1.99	151.24
SalesOrders

Cell Formulas
Range	Formula
F2:F44	F2	=E2*D2

I think my question is either very difficult or easy that no one is able to help. Ok then, just please let me know how to generate unique values like first 3 columns in first table? i.e., region, s# and representative!!??

Thanks in anticipation........