How do you create three dimensional arrays and assign their values from mult worksheets?

Hi all,

I'm a beginner in excel VBA and I would like to know if you can help me start creating my macro. Here's the context of the problem:

Assume an automotive manufacturing plant that has 4 different lines to create 4 different vehicles. Each line has their own key performance indicators. Each worksheet represents different line by week. The desired output would be graphs showing the performance of each line by week. What I imagine is a dynamic graph with a drop-down list to change the line, where y-axis is the OEE and the x-axis is weekly OEE.

I assume that this would require a three dimensional array (Line x, performance indicator y, week z) and some of these inputs would come from the worksheet name that might or might not be standardize. I attach the sample of the input and I would really appreciate if you have any input.

Thanks!

Hi,

Thank you for your reply. I find the approach very interesting. But since I'm a beginner, I'm still having trouble understanding the 2 macros you have.

However, while waiting for replies, I also have made a wee bit of progress on my own and I successfully store values into arrays and copy that into my "Overview" tab.

From here, I would like to create one line chart which the user can change using a combobox (i.e. Line 1, Line 2, etc).

Note: The number of week and line do vary depending on the input. Thus, the table on the overview tab might also change in size.

Thank you!

Originally Posted by oeldere

Don't use merged cells, you get in trouble with it sooner or later.

Maybe like this.

2 macro's

After that I made an table of the data.

After that I made an pivot table.

After that I made 2 graphs.

Please reply.