VBA Macro to create new worksheet, charts, etc.

Hi,

I need a macro to create the following worksheets and charts from an Excel data set:

Three (3) worksheets (already created manually in attached Excel file):

1. Chart Data.
(a) Column A in Chart Data is always numbered 1 - 600 (50 years x 12 mos/yr).
(b) Column I and column Y data sets (from Prod_Month) created in Chart Data. Each data set can be identified and collated with column F in Prod_Month (API) which is unique for each dataset.

2. Rate vs. Month - plot of Daily Gas (col. Y in Prod_Month) vs. Months (col. A in Chart Data).

3. Rate vs. Time - plot of Daily Gas (col. Y in Prod_Month) vs. Calender Time (col. B et. al. in Chart Data)

At a minimum, could someone help me create the Chart Data worksheet from the data in PROD_MONTH? Charting all the columns takes time from Chart Data but any data manipulation macro(s) help.

Thanks,

Jim15

have you tried to create a macro yourself?
Because this sounds like something you just record and fiddle with and it'll work for you...

Below is code to format your data, but before I go any further with this, can you paste this code into your Prod_Month sheet, run it and confirm whether that's how your data should be arranged? If so, then I'll put the chart code together ...

Please Login or Register  to view this content.

If this is not right, then let me know where I've gone wrong & I'll take another look. Cheers, MM.

you're very welcome Jim ... "don't let the b$%^&ds get you down"

Of course, this only does the chart data prep; let me know if you'd like me to also code the chart part of your question ...

Sure no probs ... in the middle of something so may be an hour or so ...

... spoke too soon .. I've just looked at your chart layouts and since in the full set of data you've got 11 groups of data, your charts in this format are going to look a real mess ... are you sure that's how you want them? Perhaps you could run my code in your book and manually configure the charts so I can confirm how you want them to look?

Hi Jim .. yep - that's do-able ... It also allows you to have them all or just those of interest (I knew what you meant )... So if you can manually configure the charts how you want them set up, and reattach your workbook, I'll write a little routine to change the datasets based on which ones a user selects. Just make sure the charts are set up as you want to see them (e.g. I noticed one of them had a logorithmic scale which of course will give an error if zeroes or negative values are in the data). If it really is a log scale, then it will change how I can define the range. Also - can you explain how you use the 1-600 scale in defining the x axis?

Hi jim ... charting solution is attached. It is triggered when you leave the chart data sheet and it looks to see if any of the well names in row 1 are selected; so if you select the whole of row 1, all well wills be plotted; if you select individual or multiple cells, then those are plotted when you leave the sheet. Of course, you can change whatever triggers the plotting - use a button or whatever suits. Code for the charting bit is below.

Please Login or Register  to view this content.

Hope that helps. MM.

MM,

I reviewed the VBA Macro and the charting function plots the incorrect date with the data. It should be selecting the Month-YY column immediately before the data instead of after.

Do you know which line of VBA tells what column to use or can you fix?

Thanks,

Jim15

Hi Jim - yep, sorry about that ... the offending line is:

Please Login or Register  to view this content.

... which of course should be:

Please Login or Register  to view this content.

I had the offset to the right instead of the left - doh! Have corrected it for you and it's in the attached.