Using VBA to create a dynamic chart with data from multiple sheets

[dops781]

Hi, I am new to VBA and I want to create a dynamic line chart with a drop down for student name(name of the worksheets), to display one student at a time. The chart will plot total score over time by booklet number (1 – 18, 19 – 30). The chart range is A7 to Z column. The dates(x-axis) are in A column, booklet numbers(series) in C column and total score(y-axis) in Z column. Since the dates can be added there is no limit to the number of rows. There are about 50 students so 50 worksheets worth of data that I want to compile in the chart. There should also be the ability to add new students. Please any help will be appreciated.

 

[oldbrewer]

I would use a macro to fetch the data - is that acceptable ?