+ Reply to Thread
Page 1 of 3 1 2 3 LastLast
Results 1 to 15 of 39

Auto-populate data to a master worksheet from other sheets in a shared workbook

  1. #1
    Registered User
    Join Date
    05-08-2009
    Location
    Scotland
    MS-Off Ver
    Excel 2000
    Posts
    3

    Auto-populate data to a master worksheet from other sheets in a shared workbook

    I have never really used VBA and so am completely stuck at this problem. I need to create a macro which auto-populates a master worksheet from the individual user sheets in a shared workbook.

    Sheet 1 is the master sheet "Team Stats". There will be an undetermined number of individual worksheets to accomodate new staff.

    Each worksheet will be identical, using columns A-I with row 1 having the headings:

    Date, Name, Reference, Value, Price, Age, Purchased?, Destination, Add. Products (the last 3 columns will have a drop-down list which will be used to enter data into the cell).

    There will be a varying number of rows in each of the individual sheets.

    If possible I would like the macro to run every time data is entered into one of the individual worksheets. If this is not then it would be fien to update every time the workbook is opened.

    If anyone can help it would really cut down the time I spend collating these stats every day!
    Last edited by JennyJAL; 05-12-2009 at 04:00 PM.

  2. #2
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,767

    Re: Auto-populate data to a master worksheet from other sheets in a shared workbook

    Please try the attached. I've assumed that there will always be a value in the first column.
    Attached Files Attached Files
    Last edited by mrice; 05-12-2009 at 03:58 AM.
    Martin

    If my solution has saved you time and/or money, please consider donating to Cancer Research UK or your local equivalent.

    https://www.cancerresearchuk.org/

  3. #3
    Registered User
    Join Date
    05-08-2009
    Location
    Scotland
    MS-Off Ver
    Excel 2000
    Posts
    3

    Smile Re: Auto-populate data to a master worksheet from other sheets in a shared workbook

    This seems to work perfectly, thank you for your help!

  4. #4
    Registered User
    Join Date
    07-02-2009
    Location
    Houston, Texas
    MS-Off Ver
    Excel 2007
    Posts
    1

    Re: Auto-populate data to a master worksheet from other sheets in a shared workbook

    What was the macro that you used? I am having a similar problem with trying to link workbooks to one mastersheet. The template you sent works, but I am unable to save it in order to use it. Thanks!

  5. #5
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,767

    Re: Auto-populate data to a master worksheet from other sheets in a shared workbook

    I guess that you are using Excel 2007. If this is the case, you may need to save as a macro enabled workbook with a .xlsm extension.

  6. #6
    Registered User
    Join Date
    11-08-2011
    Location
    Melbourne
    MS-Off Ver
    Excel 2003
    Posts
    1

    Re: Auto-populate data to a master worksheet from other sheets in a shared workbook

    Quote Originally Posted by mrice View Post
    Please try the attached. I've assumed that there will always be a value in the first column.
    Hello Champions!

    I have tried to use the attached sheet. but it seems it is not solving my problem which is quite similar to this.

    1. I have Shared workbook with 10 user using individual W.SHEET.
    2. 1 master W.SHEET.

    what i want:
    1. After each user fill out the table in their individual sheet. that data(record) should goes to next available row in master sheet.
    2. That last updated master sheet should also get updated in all other open views. so the next person will done entering his data in table, then he can use the next available row without any conflict of changes.

  7. #7
    Registered User
    Join Date
    11-17-2011
    Location
    Wisconsin, USA
    MS-Off Ver
    Excel 2007
    Posts
    1

    populate data from other worrksheet depending on the value selected in the dropdown

    Hi, I am Skhay.

    I am trying get a formulae for the requirement as mentioned:
    sheet1 A1 - name of the person, sheet1 b1 is the name of the month (provided as dropdown), sheet1 c1 is the expected result. In the same workbook, i have other worksheets named as the months (as displayed in sheet1 b1). In those worksheets, the same list of persons are listed and there is a numeric value against thier name in c1 of respective worksheet.

    Now i need to display the result in sheet1 c1 dynamically depending on the month selected in the dropdown (b1).
    For Example: sheet1 a1: XYZ, sheet1 b1: NOVEMBER, in the worksheet named NOVEMBER, against the person XYZ (in a1) there is a value 2277 (in c1). Now this value 2277 has to be dynamically populated in sheet1 c1.

    Basically formulae iam looking for is: if the value in the dropdown selected matches the worksheet name, then search for the person name in 'a' column and get the value from corresponding 'c' column.

  8. #8
    Registered User
    Join Date
    12-29-2011
    Location
    Saint John, Nb, Canada
    MS-Off Ver
    Excel 2003
    Posts
    1

    Re: Auto-populate data to a master worksheet from other sheets in a shared workbook

    Hi I sooo hope you will read this ... THIS IS EXACTLY WHAT I NEED ... What is the Macro you used...
    I NEEDDD it thanks a bunch for the help

  9. #9
    Registered User
    Join Date
    11-03-2011
    Location
    Seattle, Washington
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: Auto-populate data to a master worksheet from other sheets in a shared workbook

    Quote Originally Posted by jvalor View Post
    What was the macro that you used? I am having a similar problem with trying to link workbooks to one mastersheet. The template you sent works, but I am unable to save it in order to use it. Thanks!
    I have the same need for this macro. Could you please tell me what you did to get this to work like it does?

  10. #10
    Registered User
    Join Date
    12-17-2011
    Location
    mumbai
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Auto-populate data to a master worksheet from other sheets in a shared workbook

    Quote Originally Posted by mplagace View Post
    Hi I sooo hope you will read this ... THIS IS EXACTLY WHAT I NEED ... What is the Macro you used...
    I NEEDDD it thanks a bunch for the help
    if my file is at other source then what will be the code??????

  11. #11
    Registered User
    Join Date
    07-22-2013
    Location
    Houston, Texas
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Auto-populate data to a master worksheet from other sheets in a shared workbook

    I thank you for the above information as it is helping me put together a wedding invite list. My sheet 1 is "Master List" and the following sheets list out my family, my bride's family, my friends, etc. I was wondering if there was a way to make the sheet 1 "Master List" populate in alphabetical order? Thanks in advance.

  12. #12
    Registered User
    Join Date
    08-29-2013
    Location
    Brampton, On
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Auto-populate data to a master worksheet from other sheets in a shared workbook

    Quote Originally Posted by mrice View Post
    Please try the attached. I've assumed that there will always be a value in the first column.
    Hello

    This worked as well with what I needed to do, but I was wondering. I am using part numbers and QTY on 7 tabs ( different sections of macahines). The same part number could be used in the different sections (o-ring as an example) is there a way that when it auto populates on the master sheet that it can tally and provide a grand total if there are duplicates entered?

    Thank you

    Mel
    Last edited by Melyd; 08-29-2013 at 04:08 PM. Reason: deleting to start new thread

  13. #13
    Registered User
    Join Date
    12-06-2013
    Location
    United States
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: Auto-populate data to a master worksheet from other sheets in a shared workbook

    Quote Originally Posted by mrice View Post
    Please try the attached. I've assumed that there will always be a value in the first column.
    First time poster here and I know I am going to get flamed for resurrecting an old thread. This worked perfect for me also but how would I change the code to work with only certain worksheets. I have a lot of worksheets but I only need it to compile data from a couple.

    Please help me.

  14. #14
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    11,911

    Re: Auto-populate data to a master worksheet from other sheets in a shared workbook

    It appears that this thread has been hijacked by more than 4 people.
    Jason,Melyd and others need to start you won threads.
    Please read forum's rule.

  15. #15
    Registered User
    Join Date
    06-07-2014
    Posts
    8

    Re: Auto-populate data to a master worksheet from other sheets in a shared workbook

    Hello mrice - I am responding to a very old post I found but your spreadsheet you attached is so close to what I am looking for thought you might be able to help

    In your spreadsheet the individual tabs auto populated the Master sheet.

    My master Sheet has multiple columns / headers labelled across the top like below

    Company Contact Industry Sector Sales Stage Priority Country Competitor Held Account Comments


    I have 25 other identical spreadsheet tabs labelled by individual countries - all the countries in Africa

    I want to go to the master sheet, select a country from a dropdown menu, and then start filling in all the other columns on the master and have that information auto populate the individual country sheet with the same information. If I select Angola on master and enter all my data on the master, the Angola sheet auto populates and so on with each country I select on the master.

    Any advice

+ Reply to Thread
Page 1 of 3 1 2 3 LastLast

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