Creating a Clustered Column Graph from two separate tables?

I'm creating a macro to generate several graphs based on a bunch of results in various tables.

One of these graphs needs to be a clustered column with bars representing the first set of data and lines representing the second (example on second tab of what I'm aiming for).

I have this same code set up for a very similar macro so I know it works (I've simplified it for the example to only show the issue I'm facing).

The problem is that for this one, the two sets of data are found within different tables so they aren't on the same rows (unlike my original macro) and therefore excel treats it as one large set of data instead of two smaller sets.

As you can see, both tables share the same names from the "Variety" column and this will be how I identify them on the chart (see second tab).

Is it possible to point VBA to the name columns and "merge" them so that it treats it as two columns of data from the same set? Maybe using a VLOOKUP or similar function? One thought I had was to get VBA to put the data next to each other, produce the chart, paste it as an image and then delete the copied data but that won't allow me to update the chart by changing the input data.

The blue highlighted cells will be the two sets of data used for one chart, the green will be used for a separate one.

Any suggestions?

Managed to solve my own problem by splitting up the source input and creating series individually.

Please Login or Register  to view this content.