+ Reply to Thread
Results 1 to 24 of 24

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

  1. #1
    Forum Contributor
    Join Date
    04-30-2009
    Location
    USA
    MS-Off Ver
    Excel 2016
    Posts
    496

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

    Hello all. Please see attached workbook. I have a sheet "Gantt Chart" that I am using as a template to track projects. The code on the sheet werks exactly as desired but when I copy "Gantt Chart" to a new sheet, the code does not work on the new sheet. The named ranges are created dynamically and look good and all the sheet code is intact but when I add new tasks, they are not populating in the Gantt Chart on the new sheet. Since users will be using this to creat their own tracking charts, the code needs to work every time a new sheet is copied.

    Any help would be appreciated correcting this problem.

    thanks,
    Andrew
    Attached Files Attached Files
    Last edited by drewship; 10-21-2010 at 09:08 AM.

  2. #2
    Forum Contributor
    Join Date
    04-30-2009
    Location
    USA
    MS-Off Ver
    Excel 2016
    Posts
    496

    Re: 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

  3. #3
    Forum Contributor
    Join Date
    04-30-2009
    Location
    USA
    MS-Off Ver
    Excel 2016
    Posts
    496

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

    Found the below code in the Forum and although it copies and renames the entire sheet codes and all but this still did not add new tasks to the chart when I add them to the sheet.

    Please Login or Register  to view this content.
    Anyone have any possible solutions?

    Thanks,
    Andrew
    Last edited by drewship; 10-14-2010 at 12:29 PM.

  4. #4
    Forum Contributor
    Join Date
    04-30-2009
    Location
    USA
    MS-Off Ver
    Excel 2016
    Posts
    496

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

    Can anyone help?

    Thanks,
    Andrew

  5. #5
    Forum Contributor
    Join Date
    04-30-2009
    Location
    USA
    MS-Off Ver
    Excel 2016
    Posts
    496

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

    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

  6. #6
    Forum Guru
    Join Date
    08-26-2007
    Location
    London
    Posts
    4,607

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

    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?

  7. #7
    Forum Contributor
    Join Date
    04-30-2009
    Location
    USA
    MS-Off Ver
    Excel 2016
    Posts
    496

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

    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

  8. #8
    Forum Guru
    Join Date
    08-26-2007
    Location
    London
    Posts
    4,607

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

    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.

  9. #9
    Forum Contributor
    Join Date
    04-30-2009
    Location
    USA
    MS-Off Ver
    Excel 2016
    Posts
    496

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

    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

  10. #10
    Forum Contributor
    Join Date
    04-30-2009
    Location
    USA
    MS-Off Ver
    Excel 2016
    Posts
    496

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

    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

  11. #11
    Forum Guru
    Join Date
    08-26-2007
    Location
    London
    Posts
    4,607

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

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

  12. #12
    Forum Contributor
    Join Date
    04-30-2009
    Location
    USA
    MS-Off Ver
    Excel 2016
    Posts
    496

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

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

    Thanks,
    Andrew
    Attached Files Attached Files

  13. #13
    Forum Guru
    Join Date
    08-26-2007
    Location
    London
    Posts
    4,607

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

    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).

  14. #14
    Forum Contributor
    Join Date
    04-30-2009
    Location
    USA
    MS-Off Ver
    Excel 2016
    Posts
    496

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

    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

  15. #15
    Forum Contributor
    Join Date
    04-30-2009
    Location
    USA
    MS-Off Ver
    Excel 2016
    Posts
    496

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

    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
    Attached Files Attached Files

  16. #16
    Forum Contributor
    Join Date
    04-30-2009
    Location
    USA
    MS-Off Ver
    Excel 2016
    Posts
    496

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

    Made a few changes in the attached workbook (moved the 3 columns used for the chart next to each other) in the hopes of making this easier but still need help. In the below code, I am trying to select the range for the chart starting in C7 which needs to be the Verticle axis and label, to E200 (want the actual range to add rows dynamically) where the calculations in columns D and E are combined to display the bar on the horizontal axis representing the % of the task completed. If you add some rows to the GanttChart sheet, you will see how the calculations in combination with the code, color the bar as the % completed is changed. I need this same functionality for the charts created via the below macro once the data has been added/updated.

    Please Login or Register  to view this content.
    Thanks for your assistance,
    Andrew
    Attached Files Attached Files
    Last edited by drewship; 10-18-2010 at 11:49 AM.

  17. #17
    Forum Contributor
    Join Date
    04-30-2009
    Location
    USA
    MS-Off Ver
    Excel 2016
    Posts
    496

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

    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

  18. #18
    Forum Contributor
    Join Date
    04-30-2009
    Location
    USA
    MS-Off Ver
    Excel 2016
    Posts
    496

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

    Ok...here is my latest code and workbook. This correctly starts and ends the chart at the minimum and maximum dates of the project and the bars correctly display the desired colors based on the completed percentage entered in column H. I would like to have this % displayed on the bar if possible but this is not a show stopper since it is displayed in column H. I am still looking for help to angle the dates at the top of the chart to a -45 degree angle so they display going up from left to right. I also need to hide columns D and E but still have their calculations performed. And most inportantly, the sheets need to be created and changed dynamically. I have the sheet name "Test" hard coded in for testing but this needs to be a variable so the user can input a name. Any and all help is appreciated!!

    Please Login or Register  to view this content.
    Thanks,
    Andrew
    Attached Files Attached Files
    Last edited by drewship; 10-19-2010 at 11:45 AM.

  19. #19
    Forum Guru
    Join Date
    08-26-2007
    Location
    London
    Posts
    4,607

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

    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.

  20. #20
    Forum Contributor
    Join Date
    04-30-2009
    Location
    USA
    MS-Off Ver
    Excel 2016
    Posts
    496

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

    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

  21. #21
    Forum Contributor
    Join Date
    04-30-2009
    Location
    USA
    MS-Off Ver
    Excel 2016
    Posts
    496

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

    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

  22. #22
    Forum Contributor
    Join Date
    04-30-2009
    Location
    USA
    MS-Off Ver
    Excel 2016
    Posts
    496

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

    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

  23. #23
    Forum Guru
    Join Date
    08-26-2007
    Location
    London
    Posts
    4,607

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

    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!)

  24. #24
    Forum Contributor
    Join Date
    04-30-2009
    Location
    USA
    MS-Off Ver
    Excel 2016
    Posts
    496

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

    I thought about that but I placed it here since this involve coding. I will post this to the Charts section and link back since I think Andy would probably know the answer.

    Link to thread in Charting:

    http://www.excelforum.com/excel-char...ml#post2403135

    Thanks,
    Andrew
    Last edited by drewship; 10-20-2010 at 09:07 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1