+ Reply to Thread
Results 1 to 10 of 10

Is It Possible To Apply Changes To Multiple Sheets

  1. #1
    Registered User
    Join Date
    02-02-2010
    Location
    playa del carmen, mexico
    MS-Off Ver
    ms office 2007
    Posts
    52

    Is It Possible To Apply Changes To Multiple Sheets

    I am currently designing a workbook (in Excel 2003) which will have (among other stuff) a list of houses, a separate sheet for each house, and a template for the house sheets.

    I have done it this way because down the line, more houses may get added to the list, hence the template.

    However, once I entered the details/data into each of the sheets, there may come a time when I need to add a section, some details to the template and each sheet respectively.

    Is there a way, whereby I could, for example, insert a column to the template, and have it insert a column to each and every one of the house sheets simultaneously?
    Last edited by globalpontoon; 04-16-2011 at 07:46 PM.

  2. #2
    Forum Expert Cutter's Avatar
    Join Date
    05-24-2004
    Location
    Ontario,Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,451

    Re: Is It Possible To Apply Changes To Multiple Sheets

    Yes

    You can Group the sheets. Once grouped a change on one makes an identical change on all within the group.

  3. #3
    Registered User
    Join Date
    02-02-2010
    Location
    playa del carmen, mexico
    MS-Off Ver
    ms office 2007
    Posts
    52

    Re: Is It Possible To Apply Changes To Multiple Sheets

    OK

    That makes sense.

    However, in that situation, if I was to, for example, change the House Address on House 37, would that then change the House Address on all houses.
    Or could it be set, so that only changes on the template will affect the whole group, but changes on House 37 will not.

  4. #4
    Forum Guru
    Join Date
    11-29-2010
    Location
    Ukraine
    MS-Off Ver
    Excel 2019
    Posts
    4,168

    Re: Is It Possible To Apply Changes To Multiple Sheets

    hi, globalpontoon, why not to have all the data on one main sheet and one template. If you need template with any particular house data it can be easily created using data from main sheet. You will have managable number of sheets and effective process. Besides having all the information categorized on one sheet makes it easy to do any sort of analysis.

  5. #5
    Forum Expert Cutter's Avatar
    Join Date
    05-24-2004
    Location
    Ontario,Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,451

    Re: Is It Possible To Apply Changes To Multiple Sheets

    You wouldn't make ANY changes to a sheet while they are grouped unless you want that same change on every sheet within the group.

    So when making changes on a house sheet you would make sure they are not grouped.

    When making a universal change you would first select your template then right click on the template sheet's tab. Click on Select All Sheets (if there are no there sheets other than the template and a bunch of houses). They are now grouped and ready for changes. When finished you right click on the tab again and select Ungroup Sheets. Now you can make individual sheet changes.

    To group a few sheets you can hold the shift key and click on a sheet a few sheets away from the active one. All sheets in between will be grouped. You can also group non-adjacent sheets by holding the Ctrl key while selecting them.

  6. #6
    Registered User
    Join Date
    02-02-2010
    Location
    playa del carmen, mexico
    MS-Off Ver
    ms office 2007
    Posts
    52

    Re: Is It Possible To Apply Changes To Multiple Sheets

    A lot of it has been done in that regard, with each sheet looking up information from the main sheets of data.

    However, right now it is designed to track things like Addresses, Maintenance Schedules, Rentals, and many other categories.

    I have the design template ready so that if i need to add a house, it will just copy the whole design, and be ready to start anew.

    No problem so far, however, if at some point I happen to take on a new house that has a gym with a personal trainer (or any other weird example that I just cannot conceive of right now), I would like the ability to add a section of 3 columns, with headers to the template, and have it automatically add that section to all other houses. And yes, I know this is a bad example, because it would just need to be on that one sheet only, but I think the principal behind the example is more what I am trying to explain.

    If that time comes, I could obviously code a VBA routine to do this for me if I have to, but I was just wondering if there was a way to have a worksheet_change routine or something pre-coded into the template, that takes any changes made to the template itself, and automatically transfers them to every house.

  7. #7
    Registered User
    Join Date
    02-02-2010
    Location
    playa del carmen, mexico
    MS-Off Ver
    ms office 2007
    Posts
    52

    Re: Is It Possible To Apply Changes To Multiple Sheets

    To Cutter,

    This is more like it.

    How would I code VBA to do this, because there will be somewhere in the region of 120-150 houses, along with around 100 various other sheets, so shift-clicking, and ctrl-clicking is simply not appropriate.

  8. #8
    Forum Expert Cutter's Avatar
    Join Date
    05-24-2004
    Location
    Ontario,Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,451

    Re: Is It Possible To Apply Changes To Multiple Sheets

    Sorry, I don't give VBA advice.

    If your setup is such that there are no non-house sheets between the template and the last house sheet then the shift+click is very easy to accomplish. Select the template then click on the arrow to left of the leftmost sheet tab. (Use the right end arrow to go to last sheet if that is the last house sheet, otherwise the right arrow until you can see the last house sheet's tab.) Hold the shift key while selecting that sheet and everything in between is grouped.

    Here is a link

    http://office.microsoft.com/en-us/ex...005202492.aspx
    Last edited by Cutter; 04-16-2011 at 07:29 PM. Reason: Added link

  9. #9
    Registered User
    Join Date
    02-02-2010
    Location
    playa del carmen, mexico
    MS-Off Ver
    ms office 2007
    Posts
    52

    Re: Is It Possible To Apply Changes To Multiple Sheets

    Thanks for the help Cutter,

    The suggestion you have made is the right one, however, I would appreciate a little help from somebody regarding the VBA element to this method.
    Another reason this would not work for me, is that (due to the amount of sheets) I currently run from a main sheet control panel, and by using me.visible on the worksheet_activate/deactivate routines, only the active worksheet is ever visible at any one time.

  10. #10
    Registered User
    Join Date
    02-02-2010
    Location
    playa del carmen, mexico
    MS-Off Ver
    ms office 2007
    Posts
    52

    Re: Is It Possible To Apply Changes To Multiple Sheets

    OK, through a google search I have found the basics of how to this.

    So, thanks Cutter for the point in the right direction,

    And if anybody else ever needs to know this, here is the absolute basic code format (obivously mine needs to be more specific, but that I can work out)

    Please Login or Register  to view this content.

+ 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