VBA for multiple charts from multiple ranges

[LaterPater]

Hello everyone!

I have a question about making charts with macros, on which I am sort of noob. I have tried to find the answer by looking at similar problems and do have an idea about the code, it's just hard to apply everything to my own problem. I would like to follow a course to learn more about macro's, but I am kind of in a hurry.

In the HTML (bottom of post) there is a table from which I want to create graphs, I would like to make a chart for every company in the table (this is a shortened file, in the original file I have about 250 companies).

The data is dynamic: the number of companies will change, the company names will change, the series data will change and the dates will change. The STAFF type will usually be the same, but when I want to add a staff type now it means i will have to reselect the data for each chart. The company names will be generated in the tables with a pivot table. Please note that the first chart will always refer to the first company and so on, although the company itself will change.

What I would like to do is have a macro that will generate a chart for each company on the worksheet 'Charts'. The macro will have to select the data for the first chart ($D$1:$AA$8), make the chart, then 'offset' the range 8 cells down to $D$9:$AA$17 and repeat the LOOP until the companyname cell value is 0 (my tables generate a 0 after the last company).

Basically, I hope it is possible to specify the layout and other settings for the graphs as well (I have a template for the chart that could be applied). Also it would be helpful if it places the charts in the right position, I would like to have three charts of similar size on the same place on each page. (first 3 rows empty, then chart, 1 row empty, chart, 1 row empty, chart and then on the following pages exactly the same)

I have one file with all the charts already there, so a solution to change the data series/layout for existing charts could also work.

I hope someone can help me with this, as I have changed all the graphs manually before this and I can tell you that it is not a nice job to do. Thanks!

Hugo

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA
1	1	7	Company1		#N/B	42899	#N/B	#N/B	#N/B	#N/B	#N/B	#N/B	42906	#N/B	42908	#N/B	#N/B	#N/B	42912	#N/B	#N/B	#N/B	#N/B	#N/B	#N/B	#N/B	#N/B
2	1	6	Company1	STAFF1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
3	1	5	Company1	STAFF2	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
4	1	4	Company1	STAFF3	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
5	1	3	Company1	STAFF4	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
6	1	2	Company1	STAFF5	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
7	1	1	Company1	STAFF6	0	1	0	0	0	0	0	0	1	0	1	0	0	0	1	0	0	0	0	0	0	0	0
8	1	0	Company1	TOTAL	0	1	0	0	0	0	0	0	1	0	1	0	0	0	1	0	0	0	0	0	0	0	0
9	2	7	Company2		#N/B	#N/B	#N/B	42901	#N/B	#N/B	#N/B	#N/B	#N/B	42907	42908	#N/B	#N/B	#N/B	#N/B	42913	42914	#N/B	#N/B	#N/B	#N/B	#N/B	#N/B
10	2	6	Company2	STAFF1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
11	2	5	Company2	STAFF2	0	0	0	2	0	0	0	0	0	1	1	0	0	0	0	3	1	0	0	0	0	0	0
12	2	4	Company2	STAFF3	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
13	2	3	Company2	STAFF4	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
14	2	2	Company2	STAFF5	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
15	2	1	Company2	STAFF6	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
16	2	0	Company2	TOTAL	0	0	0	2	0	0	0	0	0	1	1	0	0	0	0	3	1	0	0	0	0	0	0
17	3	7	Company3		#N/B	#N/B	42900	42901	#N/B	#N/B	#N/B	#N/B	#N/B	42907	#N/B	#N/B	#N/B	#N/B	#N/B	42913	42914	#N/B	#N/B	#N/B	#N/B	#N/B	#N/B
18	3	6	Company3	STAFF1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1	0	0	0	0	0	0
19	3	5	Company3	STAFF2	0	0	2	0	0	0	0	0	0	0	0	0	0	0	0	0	2	0	0	0	0	0	0
20	3	4	Company3	STAFF3	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
21	3	3	Company3	STAFF4	0	0	2	1	0	0	0	0	0	1	0	0	0	0	0	2	1	0	0	0	0	0	0
22	3	2	Company3	STAFF5	0	0	2	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
23	3	1	Company3	STAFF6	0	0	2	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
24	3	0	Company3	TOTAL	0	0	8	1	0	0	0	0	0	1	0	0	0	0	0	2	4	0	0	0	0	0	0
25	4	7	Company4		#N/B	#N/B	42900	#N/B	#N/B	#N/B	#N/B	42905	#N/B	#N/B	#N/B	#N/B	#N/B	#N/B	#N/B	#N/B	#N/B	#N/B	#N/B	#N/B	#N/B	#N/B	#N/B
26	4	6	Company4	STAFF1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
27	4	5	Company4	STAFF2	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
28	4	4	Company4	STAFF3	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
29	4	3	Company4	STAFF4	0	0	1	0	0	0	0	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
30	4	2	Company4	STAFF5	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
31	4	1	Company4	STAFF6	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
32	4	0	Company4	TOTAL	0	0	1	0	0	0	0	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
33	5	7	Company5		#N/B	#N/B	#N/B	#N/B	#N/B	#N/B	#N/B	#N/B	#N/B	#N/B	#N/B	42909	#N/B	#N/B	42912	#N/B	#N/B	#N/B	#N/B	#N/B	#N/B	#N/B	#N/B
34	5	6	Company5	STAFF1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
35	5	5	Company5	STAFF2	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
36	5	4	Company5	STAFF3	0	0	0	0	0	0	0	0	0	0	0	1	0	0	1	0	0	0	0	0	0	0	0
37	5	3	Company5	STAFF4	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
38	5	2	Company5	STAFF5	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
39	5	1	Company5	STAFF6	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
40	5	0	Company5	TOTAL	0	0	0	0	0	0	0	0	0	0	0	1	0	0	1	0	0	0	0	0	0	0	0
41	6	7	Company6		#N/B	#N/B	#N/B	#N/B	#N/B	#N/B	#N/B	#N/B	#N/B	#N/B	#N/B	#N/B	#N/B	#N/B	#N/B	42913	#N/B	#N/B	#N/B	#N/B	#N/B	#N/B	#N/B
42	6	6	Company6	STAFF1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
43	6	5	Company6	STAFF2	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
44	6	4	Company6	STAFF3	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
45	6	3	Company6	STAFF4	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1	0	0	0	0	0	0	0
46	6	2	Company6	STAFF5	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
47	6	1	Company6	STAFF6	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
48	6	0	Company6	TOTAL	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1	0	0	0	0	0	0	0
49	7	7	Company7		#N/B	42899	42900	#N/B	42902	42903	42904	42905	42906	42907	42908	42909	42910	#N/B	42912	42913	42914	#N/B	#N/B	#N/B	#N/B	#N/B	#N/B
50	7	6	Company7	STAFF1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	10	2	0	0	0	0	0	0
51	7	5	Company7	STAFF2	0	0	1	0	4	0	1	3	0	0	1	2	1	0	0	3	4	0	0	0	0	0	0
52	7	4	Company7	STAFF3	0	0	0	0	0	0	0	0	0	1	0	0	0	0	0	0	0	0	0	0	0	0	0
53	7	3	Company7	STAFF4	0	1	0	0	3	2	2	3	3	4	5	7	3	0	4	2	1	0	0	0	0	0	0
54	7	2	Company7	STAFF5	0	0	0	0	0	0	0	1	3	0	1	1	0	0	0	1	1	0	0	0	0	0	0
55	7	1	Company7	STAFF6	0	0	1	0	2	2	1	2	1	2	1	1	0	0	2	3	2	0	0	0	0	0	0
56	7	0	Company7	TOTAL	0	1	2	0	9	4	4	9	7	7	8	11	4	0	6	19	10	0	0	0	0	0	0
57	8	7	Company8		#N/B	#N/B	#N/B	#N/B	#N/B	#N/B	#N/B	#N/B	#N/B	#N/B	#N/B	#N/B	#N/B	#N/B	#N/B	#N/B	#N/B	42915	#N/B	#N/B	#N/B	#N/B	#N/B
58	8	6	Company8	STAFF1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
59	8	5	Company8	STAFF2	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
60	8	4	Company8	STAFF3	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
61	8	3	Company8	STAFF4	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
62	8	2	Company8	STAFF5	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1	0	0	0	0	0
63	8	1	Company8	STAFF6	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
64	8	0	Company8	TOTAL	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1	0	0	0	0	0
65	9	7	Company9		42898	#N/B	#N/B	42901	#N/B	#N/B	#N/B	#N/B	#N/B	#N/B	#N/B	#N/B	42910	#N/B	42912	#N/B	#N/B	#N/B	#N/B	#N/B	#N/B	#N/B	#N/B
66	9	6	Company9	STAFF1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
67	9	5	Company9	STAFF2	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
68	9	4	Company9	STAFF3	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
69	9	3	Company9	STAFF4	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1	0	0	0	0	0	0	0	0
70	9	2	Company9	STAFF5	2	0	0	1	0	0	0	0	0	0	0	0	1	0	0	0	0	0	0	0	0	0	0
71	9	1	Company9	STAFF6	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
72	9	0	Company9	TOTAL	3	0	0	1	0	0	0	0	0	0	0	0	1	0	1	0	0	0	0	0	0	0	0
73	10	7	Company10		#N/B	#N/B	#N/B	#N/B	#N/B	#N/B	#N/B	#N/B	#N/B	42907	#N/B	42909	#N/B	#N/B	#N/B	#N/B	#N/B	#N/B	#N/B	#N/B	#N/B	#N/B	#N/B
74	10	6	Company10	STAFF1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
75	10	5	Company10	STAFF2	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
76	10	4	Company10	STAFF3	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
77	10	3	Company10	STAFF4	0	0	0	0	0	0	0	0	0	1	0	0	0	0	0	0	0	0	0	0	0	0	0
78	10	2	Company10	STAFF5	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
79	10	1	Company10	STAFF6	0	0	0	0	0	0	0	0	0	0	0	2	0	0	0	0	0	0	0	0	0	0	0
80	10	0	Company10	TOTAL	0	0	0	0	0	0	0	0	0	1	0	2	0	0	0	0	0	0	0	0	0	0	0
81	11	7	Company11		42898	42899	42900	42901	42902	#N/B	#N/B	42905	42906	42907	42908	42909	#N/B	#N/B	42912	42913	42914	42915	#N/B	#N/B	#N/B	#N/B	#N/B
82	11	6	Company11	STAFF1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	2	0	0	0	0	0
83	11	5	Company11	STAFF2	1	2	0	2	2	0	0	1	2	0	0	0	0	0	1	4	2	3	0	0	0	0	0
84	11	4	Company11	STAFF3	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
85	11	3	Company11	STAFF4	0	0	0	0	0	0	0	0	0	1	0	1	0	0	2	0	0	0	0	0	0	0	0
86	11	2	Company11	STAFF5	0	1	2	2	1	0	0	2	7	8	1	11	0	0	4	1	0	0	0	0	0	0	0
87	11	1	Company11	STAFF6	0	0	0	1	0	0	0	0	2	6	2	2	0	0	2	0	0	2	0	0	0	0	0
88	11	0	Company11	TOTAL	1	3	2	5	3	0	0	3	11	15	3	14	0	0	9	5	2	7	0	0	0	0	0
89	12	7	0		#N/B	#N/B	#N/B	#N/B	#N/B	#N/B	#N/B	42905	#N/B	#N/B	#N/B	#N/B	#N/B	#N/B	#N/B	#N/B	#N/B	#N/B	#N/B	#N/B	#N/B	#N/B	#N/B
90	12	6	0	STAFF1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
91	12	5	0	STAFF2	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
92	12	4	0	STAFF3	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
93	12	3	0	STAFF4	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
94	12	2	0	STAFF5	0	0	0	0	0	0	0	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
95	12	1	0	STAFF6	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
96	12	0	0	TOTAL	0	0	0	0	0	0	0	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
Data

 

[Worf]

Welcome to the Board

Sub MultipleCharts()
Dim r As Range, i%, ch, co As ChartObject
Const safety = 20
Sheets("Table").Activate
i = 0: Set r = [d1:aa8]
Do While i < safety And r.Cells(1, 1).Offset(, -1) <> 0
    Set ch = ActiveSheet.Shapes.AddChart2(216, xlBarClustered)
    ch.Chart.SetSourceData r
    ch.Chart.Location xlLocationAsObject, "Charts"
    Set r = r.Offset(8)
    i = i + 1
Loop
Sheets("Charts").Activate
Set r = [b4]: i = 1
For Each co In Sheets("Charts").ChartObjects
    co.Left = r.Left:            co.Top = r.Top
    co.Width = [b2:h2].Width:    co.Height = [a4:a15].Height
    co.Chart.ApplyChartTemplate _
    ("C:\Users\Eddie\AppData\Roaming\Microsoft\Templates\Charts\columns.crtx")
    Set r = r.Offset(IIf(i Mod 3 = 0, -26, 13), IIf(i Mod 3 = 0, 10, 0))
    i = i + 1
Next
End Sub