Code not working on new sheet copied from a sheet that works

I noticed that the New sheet that was copied from the template has named ranges that have a scope of New while the original scope is Workbook for the named ranges. I checked the source for the chart and noticed that the source did not copy over correctly and was changed to leave out the named range statements. Once I corrected those the chart appeared, but displayed the data from the template chart.

How can users create a working copy of the sheet Gantt Chart? I should not have to go back to each new sheet and make corrections. Is there a way to make an working copy of Gantt Chart with all the sheet code and named ranges using a macro? Users will need to input thair own name for each project so this also needs to be taken into account.

Thanks,
Andrew

Can anyone help?

Thanks,
Andrew

Seems this problem may be too difficult since ideally I would like to track all the projects in the same workbook. I will look through the forum and see about using a macro to copy the entire workbook since that seems to allow the code to work as desired. This will mean multiple workbooks to sort through and I will need a way to access them to copy their charts and data back to a master workbook for reporting and metrics purposes.

Thanks,
Andrew

So the problem is if you copy the Gant sheet, the new chart is based on ranges in the original sheet? If so, could you create the chart with VBA and reference the ranges on the new sheet?

Ideally I would like to copy a blank (with the exception of the sheet code and 6 row header) Gantt Chart to a new sheet named via an InputBox. The user would then select values from the named ranges in column A and B, then enter the desired Task name. The Tasks need to be a dynamic named range specific to the new sheet as do the CompCal, Dates, and RemainCal. The Status and Users named ranges are tied to the Utils sheet and should be available to every sheet created.

Do you know of a way to accomplish this?

I was going to setup a template sheet with the first row (7) started so the rest of the sheet would work but the code did not populate the chart when the Task was entered.

Thanks,
Andrew

Could you just set up all the named ranges for the sheet using code (and the name given to the sheet). You could add the validation dropdowns in cols A&B this way. The sheet presumably references specific columns in each sheet so could work off named ranges. I rarely use charts and have never automated with VBA, but using the macro recorder you would have some foundations. I'll have a play later on if I have time.

As another note, I did try to modify the new named ranges created when the Gantt Chart was copied, but depending on the changes I made, either the new chart would not populate when tasks were added or it showed the tasks from the Gantt Chart and could not be changed on the new sheet.

Andrew

I know how to set up the validations in column A and B via code and will do that since they will not change. Not sure about the rest though...but still looking through the forum...

Thanks,
Andrew

You don't really need to name the ranges for the chart, you could reference them directly.

I will look up how to reference the dynamic chart ranges via VB. Attached is an updated workbook.

Thanks,
Andrew

I would create the chart from scratch because it is picking up the references from the original sheet (even though the Task range, for example, seems to reference the new sheet).

Found this post with Andy Pope posting code that I may or may not be able to manipulate for my use.

http://www.excelforum.com/excel-char...orksheets.html

It creats a new sheet with a chart and data from each row of data on a source sheet. I want to add the rows of data to a sheet and create a single chart using the data on the sheet.

Hopefully it will yield something useful.

Thanks,
Andrew

Did more research and the CreateChart macro is what I have come up with so far. See new workbook.

Please Login or Register  to view this content.

If you look at the Test sheet, you will see the results are not usable yet, but it is a start. I need to code in the specific ranges for columns C, H, and I and the lables should hopefully change as well so the chart looks like the one on the Chart sheet.

I also need the macro to delete the current chart (if one is there) and replace it with an updated one. The chart also needs to automatically resize to accomodate the source data if possible.

So to sum it up, I need help to incorporate creating a new sheet from the Header sheet which is named by the user via an InputBox, and once the user is finished entering in the data in the new sheet, the macro would be run to create the chart using the sources starting in row 7 of columns C, H, and I.

Thanks,
Andrew

Getting closer. I need help tweaking the following macro. I tried the macro recorder to change the following items but to no avail:

Need code to format the dates at the top of the chart to -45 degrees so they angle up from left to right.

Need code to remove the plot and dates to the left of the start date on the chart (not sure where this is coming from bit needs to be corrected somehow).

Need code to color the % Completed Calculation (currently red) to icolor=5.

Need code to color the % Remaining Calculation (currently green) to icolor=4.

Please Login or Register  to view this content.

Thanks for your assistance,
Andrew

Andrew - I really don't think you need any help. You seem to get there on your own! I see charts and tend to run in the opposite direction.

LOL...I seem to be spending a lot of time posting my updates so I guess I am getting there...just not as fast as I would like...and maybe not as elegantly either..

I found this line of code:

Please Login or Register  to view this content.

will angle the text on the X axis at a -45 degree angle...but I need the Y axis to be angled so still looking how to change this for Y.

Thanks,
Andrew

The change I needed to make is that X contains the Categories and Y contains the Values, so the change below works.

Please Login or Register  to view this content.

Next item is hiding columns D and E while maintaining the calculations performed via the code.

Andrew

Is Andy Pope out there? I went to your site but could not find any solutions. Will take a more detailed look again today.

Thanks,
Andrew

I'd forgotten there is a separate charts forum so you might consider posting there and linking to this thread. Though I don't know how many people look into that forum (might just be Andy!)