none
Populating Tab's within a workbook RRS feed

  • Question

  • I may be over my head with this one, but I am looking for a macro to auto-populate multiple tab's based off the main tab in a  workbook.

    MSG-Main tab which is a working document and holds all information that I would like to populate other tabs. Each client has a row which then lists out how many hours of each service are budgeted per client. A maximum of about 150 clients (rows) would be implemented.  Column J through AB in each row represents the data that I am looking to capture in each client tab. The row's list each client name (removed in picture) and they are organized by last name alphabetically. 

    Data Report- collects budgeted & utilized hours from each client individual client tab's and populate's automatically. This is listed with monthly columns and lists the services down the rows. This auto sums budgeted/ utilized for month and then the whole year at the end of the rows. I currently have the data report auto populating as long as I insert tabs inbetween the "first" and "last" tabs.

    Client Tab's- Each client will own tab which lists budget and then utilized hours per month. The actual name of each tab will be the client name.I would like the budgeted hours to auto-populate based off the MSG tab. Utilized hours will be manually entered. Ideally, the tab would only populate during the current month. So on January 1st, it would automatically auto-populate all tabs for January's budgeted hours. If I manually change the MSG tabs budgeted hours anytime in January it would automatically change that corresponding tab's hours only during January. 

    I would like the tab to directly relate to a row so that when I re-organize alphabetically that I do not lose the info in the tab. 

    Is it possible that each time I create a new tab that I can type in the row number and then have it autopopulate and not lose those figures if I reorganize again? If a client is disenrolled, I would like to keep their data tab and information and simply type in a ! symbol so that it does not calcuate them in the budgeted hours moving forward.

    ! & * symbols would remove the numbers from being calculated

    I can forward the workbook if necessary.

    The first image below is the MSG tab. The next image is the client 1 tab which will be replicated for each client. 

    The data report is almost identical to the client 1 tab except it is a total of all the client tabs.


    Tuesday, December 23, 2014 8:08 PM

Answers

  • Hi Jacob,

    Here's my suggestions:

    1. Firstly try to use macro recorder to record some general operations, if the recorded macros are not exactly what you want, you could also utilize some of them in your own VBA code.

    2. Try to use your favorite search engine to search for how to use VBA for Excel to manipulate the Workbooks, Worksheets, Cells, figures. You could also find code samples in MSDN document:

    Getting Started with VBA in Excel

    3. >>Is it possible that each time I create a new tab that I can type in the row number and then have it autopopulate and not lose those figures if I reorganize again?

    If you mean the "tab" is actually a Worksheet, and you want to populate some data in the newly created Worksheet, then it's possible. The Workbook has provided a NewSheet event, you could put your logic in this event handler. For example, rename the newly created Worksheet, retrieve some data from the "MSG-Main" Worksheet, and populate the data in this new Worksheet.

    Check sample code in this MSDN document about how to manipulate Worksheet:

    http://msdn.microsoft.com/en-us/library/office/ff194464(v=office.15).aspx


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, December 26, 2014 3:33 AM
    Moderator

All replies

  • hard to understand you, but sounds like all about business logic
    Friday, December 26, 2014 3:11 AM
  • Hi Jacob,

    Here's my suggestions:

    1. Firstly try to use macro recorder to record some general operations, if the recorded macros are not exactly what you want, you could also utilize some of them in your own VBA code.

    2. Try to use your favorite search engine to search for how to use VBA for Excel to manipulate the Workbooks, Worksheets, Cells, figures. You could also find code samples in MSDN document:

    Getting Started with VBA in Excel

    3. >>Is it possible that each time I create a new tab that I can type in the row number and then have it autopopulate and not lose those figures if I reorganize again?

    If you mean the "tab" is actually a Worksheet, and you want to populate some data in the newly created Worksheet, then it's possible. The Workbook has provided a NewSheet event, you could put your logic in this event handler. For example, rename the newly created Worksheet, retrieve some data from the "MSG-Main" Worksheet, and populate the data in this new Worksheet.

    Check sample code in this MSDN document about how to manipulate Worksheet:

    http://msdn.microsoft.com/en-us/library/office/ff194464(v=office.15).aspx


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, December 26, 2014 3:33 AM
    Moderator