+ Reply to Thread
Results 1 to 25 of 25

Auto Update Rows in One Sheet from Another

  1. #1
    Registered User
    Join Date
    06-28-2012
    Location
    New York, New York
    MS-Off Ver
    Excel 2007
    Posts
    15

    Exclamation Auto Update Rows in One Sheet from Another

    Hi All,

    I currently have a workbook that has three worksheets (status, issues, risks). I'm trying to find a solution that will take updates to issues and risks (including updated rows, inserted rows and deleted rows) and add/remove them to specified groups in sheet A.

    Example: Issues worksheet is a log tracking open and closed items based off of priority. I want to take the items from this worksheet that are open and critical priority and have them added to the status worksheet. As the items are closed they should be removed from the status worksheet.

    Same concept applies to risks worksheet.

    Does that make sense? Any help is greatly appreciated. I've attached a dummy file as an example.

    TJH
    Attached Files Attached Files

  2. #2
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Auto Update Rows in One Sheet from Another

    The only way i can see this happen without any issues is when the code re-creates the Status sheet each time you run the macro.

    Will this work for you?
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

  3. #3
    Registered User
    Join Date
    06-28-2012
    Location
    New York, New York
    MS-Off Ver
    Excel 2007
    Posts
    15

    Re: Auto Update Rows in One Sheet from Another

    I'm pretty sure that would work ... I'd have no issue with the page being recreated as long it keeps it's format. I however don't have the slightest clue how to create any of it!

    Thanks for your help,
    TJH

  4. #4
    Registered User
    Join Date
    06-28-2012
    Location
    New York, New York
    MS-Off Ver
    Excel 2007
    Posts
    15

    Re: Auto Update Rows in One Sheet from Another

    Hey all,

    Any suggestions?

    TJH

  5. #5
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Auto Update Rows in One Sheet from Another

    Try this code
    Please Login or Register  to view this content.
    Copy the Excel VBA code
    Select the workbook in which you want to store the Excel VBA code
    Hold the Alt key, and press the F11 key, to open the Visual Basic Editor
    Choose Insert | Module
    Where the cursor is flashing, choose Edit | Paste

    To run the Excel VBA code:
    Choose View | Macros
    Select a macro in the list, and click the Run button

    Couple of observations

    1. Make all the updates to the Issue Log and Risk Tracker and then run the macro.
    2. The conditions for the Issue Log are column G=Open and Column H=Critical.
    3. The conditions for the Risk Tracker are column G=Critical

    Let me know if anything needs to be changed.

  6. #6
    Registered User
    Join Date
    06-28-2012
    Location
    New York, New York
    MS-Off Ver
    Excel 2007
    Posts
    15

    Re: Auto Update Rows in One Sheet from Another

    This was a great first go at it! The issue update looks to be running great, but there were a few issues with the risks update ... See below:

    1) I forgot to add the Status column to the risk tracker sheet. If that could be added to the criteria that would be great.
    2) If you run the macro multiple times the updates to risk tracker on the status page will continuously add empty rows. After about the fifth run of the macro it acts funky and begins to insert rows formatted like the header. If you hit the macro enabled button 5 times you will see what I mean.

    I've attached a copy of the workbook with the macro created for your reference. Thank you so much for you help on this. It's so close!

    TJH
    Attached Files Attached Files
    Last edited by tjhart; 07-13-2012 at 02:51 PM.

  7. #7
    Registered User
    Join Date
    06-28-2012
    Location
    New York, New York
    MS-Off Ver
    Excel 2007
    Posts
    15

    Re: Auto Update Rows in One Sheet from Another

    One last question:

    In the future, if I want to add another section (Action) to status pulling from a new worksheet (Action Items) based off of the same criteria as the other sheet how do I need to go about adding that to the macro?

    Example file attached

    Thanks again,
    TJH
    Attached Files Attached Files
    Last edited by tjhart; 07-13-2012 at 02:52 PM.

  8. #8
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Auto Update Rows in One Sheet from Another

    Ok, i will need to work out the logic a lil more in detail since its inserting blank rows. Will update you shortly.

  9. #9
    Registered User
    Join Date
    06-28-2012
    Location
    New York, New York
    MS-Off Ver
    Excel 2007
    Posts
    15

    Re: Auto Update Rows in One Sheet from Another

    Thank you for all of your help on this Arlette. Please let me know if I need to provide more detail.

  10. #10
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Auto Update Rows in One Sheet from Another

    I will have something ready for you by tomorrow.

  11. #11
    Registered User
    Join Date
    06-28-2012
    Location
    New York, New York
    MS-Off Ver
    Excel 2007
    Posts
    15

    Re: Auto Update Rows in One Sheet from Another

    I just wanted to check back in with you. Any progress on this?? Again thanks so much for the help!

    TJH

  12. #12
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Auto Update Rows in One Sheet from Another

    This is the updated code
    Please Login or Register  to view this content.
    Observations
    The reason why the wrong format was duplicating was because of some unmerged cells after row 13. Ensure that the rows required to be merged are merged properly.
    If you want to duplicate the code for Action Items, just copy this part of the code again
    Please Login or Register  to view this content.
    Make the required corrections like changing
    Please Login or Register  to view this content.
    to
    Please Login or Register  to view this content.

  13. #13
    Registered User
    Join Date
    06-28-2012
    Location
    New York, New York
    MS-Off Ver
    Excel 2007
    Posts
    15

    Re: Auto Update Rows in One Sheet from Another

    I made the changes you suggested, sadly however, I'm still receiving the same errors. The action items submit under the risks section and the macro is still submitting empty rows and eventually duplicated the incorrect format after running the macro 12 times. I've reattached the file with the change so you can see what I mean.

    Again thank you for helping me with this! I look forward to hearing your suggestions.

    TJH
    Attached Files Attached Files

  14. #14
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Auto Update Rows in One Sheet from Another

    Here is the updated code
    Please Login or Register  to view this content.

  15. #15
    Registered User
    Join Date
    06-28-2012
    Location
    New York, New York
    MS-Off Ver
    Excel 2007
    Posts
    15

    Re: Auto Update Rows in One Sheet from Another

    That fixed the issue with action items posting under the incorrect section, however, now the action items section is the section inserting false rows and eventually the wrong formatting when more items are added than rows (after 12 runs).

    See attached. We are getting close!!

    Thanks,
    TJH
    Attached Files Attached Files

  16. #16
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Auto Update Rows in One Sheet from Another

    If there is any row that is unmerged in the last section, it creates an issue for the macro.

    Can we fix on a particular row count for the last section? For e.g. can we say that if the number of allotted rows for Action is 10 and the no of entries is 11, then the macro needs to insert a row and put the value in.

    If not, you just have to ensure that the required rows in each section (specially Action) are merged before you run the macro.

  17. #17
    Registered User
    Join Date
    06-28-2012
    Location
    New York, New York
    MS-Off Ver
    Excel 2007
    Posts
    15

    Re: Auto Update Rows in One Sheet from Another

    I would be fine setting the last row to a specific count and having it insert if necessary.

    Going forward if I am to create additional sections to this, for instance worksheet "milestones" would I just have to copy the code from earlier and change the last section to milestones or would there be more changes necessary?


    TJH

  18. #18
    Registered User
    Join Date
    06-28-2012
    Location
    New York, New York
    MS-Off Ver
    Excel 2007
    Posts
    15

    Re: Auto Update Rows in One Sheet from Another

    Final question I promise and I will mark this as solved ... I'm still needing help duplicating the code for other sections. Can you please tell me exactly what I need to be adding/changing when I add new sheets and sections I want to pull from? How would I pull multiple columns from my work sheets into the summary page?

    I've attached the final example. Thanks so much for you help!

    TJH
    Attached Files Attached Files

  19. #19
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Auto Update Rows in One Sheet from Another

    Looking at your new format, i dont think we can go with the "merged cells" concept to understand if we need to insert more rows, because you have the "Date Identified" column which needs to be populated too.

    I guess, we will have to go with a fixed number of rows for each section. For e.g. We will need to fix the number of rows as say 8 or 10. The macro then needs to check if the number of rows are enough to populate the data, if yes proceed, if no, insert rows and then proceed.

    Where is the date column in each tab that needs to be pulled up into the main sheet?

  20. #20
    Registered User
    Join Date
    06-28-2012
    Location
    New York, New York
    MS-Off Ver
    Excel 2007
    Posts
    15

    Re: Auto Update Rows in One Sheet from Another

    I think setting each section to a fixed number of 12 rows would work and then running the if else to check if there is a need for inserting more. I should of added these extra sections into the main sheet before so there wouldn't be so much rework ... I apologize for the late addition.

    Ultimately, I would like to take in 3 columns from the other sheets and pull them into the main sheet (status). These three columns are Column B (#), Column D (Description), and Column E (Date Identified) and have them inserted into Columns B, C, and D on the main sheet (status). I would like for Column C on the status sheet to format as wrapped text in order to fit longer descriptions.

    I've attached the updated template for reference.

    Thanks so much,
    TJH
    Attached Files Attached Files

  21. #21
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Auto Update Rows in One Sheet from Another

    Try this code and let me know if anything needs to be changed. I have not used the 12 rows per section logic but made the report a lil flexible.
    Please Login or Register  to view this content.

  22. #22
    Registered User
    Join Date
    06-28-2012
    Location
    New York, New York
    MS-Off Ver
    Excel 2007
    Posts
    15

    Re: Auto Update Rows in One Sheet from Another

    Hey Arlette,

    I tried to use your code in the attached work sheet and received a run time error. Which template file did you use when you built the macro?

    TJH

  23. #23
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Auto Update Rows in One Sheet from Another

    I used the copy in post 20.

    What is the runtime error and which line is highlighted?

  24. #24
    Registered User
    Join Date
    06-28-2012
    Location
    New York, New York
    MS-Off Ver
    Excel 2007
    Posts
    15

    Re: Auto Update Rows in One Sheet from Another

    Runtime error 1004: cannot change part of merged cell. I've attached the file with the updates and error:
    Attached Files Attached Files

  25. #25
    Registered User
    Join Date
    06-28-2012
    Location
    New York, New York
    MS-Off Ver
    Excel 2007
    Posts
    15

    Re: Auto Update Rows in One Sheet from Another

    Any luck?

    TJH

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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