# Microsoft Office Application Help - Excel Help forum > Excel Programming / VBA / Macros >  >  Auto-populate data to a master worksheet from other sheets in a shared workbook

## JennyJAL

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!

----------


## mrice

Please try the attached. I've assumed that there will always be a value in the first column.

----------


## JennyJAL

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

----------


## jvalor

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!

----------


## mrice

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.

----------


## nirav83

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

----------


## skhay

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.

----------


## mplagace

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

----------


## StephenAF

> 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?

----------


## rahulbawkar2006

> 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??????

----------


## cdwelch4

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.

----------


## Melyd

> 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

----------


## Jason Lash

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

----------


## AB33

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.

----------


## velvethammer

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

----------


## mrice

I expect that the moderators will be asking you to start a new thread on this one, it seems to have been popular over the years. Can you do so and I'll look out for it if someone else doesn't provide a solution before me.

----------


## mfsunshine

For anyone still looking, the Macro was embedded in the first tab:




```
Please Login or Register  to view this content.
```

----------


## Pradeepsahukari

Hi..i downloaded this file but ..only plain Data..there is nothing is changed when i entered data in first sheet..

----------


## mrice

You might need a line like




```
Please Login or Register  to view this content.
```

----------


## VBA Magic

Great Macro. Does anyone know how I can also add info/date to the team stats sheet/ master sheet and it also populates the relevant work sheets with that information ( a two way updating macro between sheets so to speak)

Many thanks

----------


## FDibbins

VBA Magic, welcome to the forum  :Smilie: 

Unfortunately _your post does not comply with Rule 2 of our Forum_ RULES. *Do not post a question in the thread of another member -- start your own thread.* 

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread. 

Old threads are often only monitored by the original participants.  New threads not only open you up to all possible participants again, they typically get faster response, too.

----------


## Doc.AElstein

Hi VBA Magic....





> VBA Magic, welcome to the forum ........ -- start your own thread.
> 
> If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread. .......



. And if you do that and PM ( Private Message ) ** me and give me a llink to the new Thread, then I will do it for you tomorrow.. but only if you give me somehow  
Good before and after “ Pictures “ as well as your explanations so i can see exactly what you want..

.  The Before or Befores,  should look just as it / they does before running of any macro.
.
.  The After or Afters, should be hand filled by you so that it they / looks exactly as you want it to After running of any macro, based on the actual sample data in the Before / or Befores
 . Make sure there is just enough data to demonstrate your need. Remember to desensitize the data if necessary.

To do that, try to 
. 1 ) use the Forum Tools in my signature to produce screen shots we can copy to a spreadsheet ( NO IMAGES ! )
. 2 ) Post Files
. 2 a) To Attach a sample workbook: 
View Pic
http://www.excelforum.com/members/da...ch-a-file.html
http://tinyurl.com/oenwprw
. 2 b) Send over a file sharing site, such as this free thing
Box Net,
https://www.box.com/
http://tinyurl.com/7chr7u8
. Remember to select Share and give the link / links they give.
. 3 c) Only as a last resort, P.M. me and i will reply with my Email Addressee so you can send me a file
** To PM me, click on my name in the left hand margin when you are logged in, the rest should be obvious.

Alan

----------


## Pete_UK

Alan,

he's started a new thread of his own now.

Pete

----------


## Doc.AElstein

> Alan,
> 
> he's started a new thread of his own now.
> 
> Pete



OK, found it, thanks.
Alan

----------


## jchung369

It seems though if you delete the data on the other sheets it drops off the Team Stats sheet.
Is there any way to keep what was entered initially and have it keep adding?

Referencing your Book1 file. mrice

----------


## dshamp

Hey All-
Can anyone help me figure out what is wrong with my Macro in the attached workbook.
I have sales reps that are populating their own sheets and I want those result to populate the master.
I have an error and I cant figure out how to solve it.
I am totally new at this so its a copied Macro, any help would be appreciated.
Thanks
Dan

----------


## protonLeah

dshamp,
Unfortunately _your post does not comply with Rule 2 of our Forum_ RULES. *Do not post a question in the thread of another member -- start your own thread.* 

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread. 

Old threads are often only monitored by the original participants.  New threads not only open you up to all possible participants again, they typically get faster response, too.

----------


## Simple_man916

I tried applying same code to my "Master" sheet and get a debug error.

Highlighted: "For Each Sheet In Me.Parent.Sheets"

My Master sheets also has added columns that two of my other sheets don't have. Could this be the issue?

----------


## protonLeah

Simple_man916, did you see post #27?
Unfortunately _your post does not comply with Rule 2 of our Forum_ RULES. *Do not post a question in the thread of another member -- start your own thread.* 

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread. 

Old threads are often only monitored by the original participants.  New threads not only open you up to all possible participants again, they typically get faster response, too.

----------


## Himanshuktw

Hi,

I am trying to consolidate different workbook in a one  master file, the code used earlier one but it's not work.

the row count is not fixed it is variable.

thanks in advance.
Himanshu

----------


## Doc.AElstein

Hi Himanshuktw
Welcome to the Forum  :Smilie: 

The original subject of this Thread is automatically consolidating data from worksheets in a single Workbook. The data from all but a Master Worksheet is consolidated into the Master Worksheet automatically.

You appear to be asking a different question related to consolidating data from a number of workbooks into a main Workbook.

If this is the case, then you need to start a new Thread in the VBA Sub Forum:
http://www.excelforum.com/excel-programming-vba-macros/
Start a new Thread there:
https://www.excelforum.com/newthread...=newthread&f=7

It would help a lot if you give us a small amount of mock up data. Keep it representative but not necessary real data.
Show us 
_ 1. the *start* situation. The *Before*
_ 2. Give us an *After*. This *After*  should be hand made by you to look exactly as you what  the macro to produce for you based on your *Before*

_.___________________________-

Just for quick info regarding finding the last row: 



> the code used earlier one but it's not work.
> the row count is not fixed it is variable......



There are many ways to determine the last row in a worksheet.
For example:



```
Please Login or Register  to view this content.
```


"MySheetName" is your Worksheet name

LastRow is an Integer Number, in this case greater or equal to one. The number returned from the above code, LastRow,  is determined  by looking back Up from the last cell in column 1 (.Item(Rows.Count, 1) ),  until the last cell with data in it is found. This  cell which is retuned from the .End(xlUp) process of looking back up, then has the .Row Property applied to it which returns the Row number of that cell.

If your last data was in column 2 , then the code line would be:



```
Please Login or Register  to view this content.
```


_.____________________________
_............................................

Please, also,  if you get time, take some time before posting to read the Forum Rules here
http://www.excelforum.com/forum-rule...rum-rules.html
http://www.excelforum.com/forums-rules/
http://www.excelforum.com/developmen...ml#post4606484
It is usually worth it in the long run.


*Thanks*
Alan
 :Wink:   :Smilie:

----------


## Himanshuktw

Hi, 

Thanks for updating.

My question is here, i need a code for consolidate multiple workbook from sheet2 in one master file.

All the files save in a same folder.

Thanks 

Himanshu

----------


## protonLeah

Himanshuktw,
Unfortunately _your post does not comply with Rule 2 of our Forum_ RULES. *Do not post a question in the thread of another member -- start your own thread.* 

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread. 

Old threads are often only monitored by the original participants.  New threads not only open you up to all possible participants again, they typically get faster response, too.

----------


## Doc.AElstein

Himanshuktw,

I think many of us can easilly help you,

*But:*
 You must *start a new Thread*: 
Ask your question here: https://www.excelforum.com/newthread...=newthread&f=7 ( *Post New Thread* )

Also, give us sample data to show us what you have and what you want the macro to do

Alan

----------


## chevyfam661

Hello all! Hope I'm not hijacking as it's definitely not my intent.

This thread describes exactly what I need! I realize it's an old post however, and I am currently running EXCEL 2010. 

I would be happy to share my file as I have tired of entering data manually. I will be adding more sheets and they will all have the same format. 

If anyone can help, I would greatly appreciate it!

----------


## Glenn Kennedy

You are hijacking!!!  Best to start your own thread. Also, please do attach a sample file.  I'll not be helping you as I don't do VBA.  But someone will.

----------


## chevyfam661

Thank you much and my apologies. 

I will do that, thanks again!!

----------


## gumshed

Private Sub Worksheet_Activate()
Dim Sheet As Worksheet
For Each Sheet In Me.Parent.Sheets
    If Sheet.Name <> Me.Name Then
        If Sheet.Cells(Rows.Count, 1).End(xlUp).Row <> 1 Then
            Sheet.Range(Sheet.Cells(2, 1), Sheet.Cells(Sheet.Cells(Rows.Count, 1).End(xlUp).Row, 10)).Copy Destination:=Me.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)

        End If
    Else
        Me.Range(Cells(2, 1), Cells(Rows.Count, 10)).Clear
    End If
Next Sheet
End Sub

----------


## protonLeah

gumshed, You are hijacking!!!
Unfortunately _your post does not comply with Rule 2 of our Forum_ RULES. *Do not post a question in the thread of another member -- start your own thread.*

----------


## kevin65811

This is great but how do I extend the code so I can include additional columns and rows in each worksheet?

----------


## Pete_UK

Do you not understand the comments above about hijacking? Please start your own new thread, referring back to this one if you think it is relevant.

Pete

----------


## FDibbins

> This is great but how do I extend the code so I can include additional columns and rows in each worksheet?



how can you NOT have seen all the posts above telling others to start their own thread???  is there something special about you that you feel this instruction should not apply to you?

----------


## FDibbins

To ALL others that would ask questions regarding this thread...DONT!! Start your own thread (and READ our rules!)

----------


## yaraalawami

Can you help me with my sheet too? I have the same request, not sure what code to put in or how

----------


## AliGW

*Administrative Note:*

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #4 about hijacking.

Please continue here in your own thread: https://www.excelforum.com/excel-pro...ter-sheet.html

----------


## FDibbins

I am SOOOOO tempted to just ban the op from post #44...htf can they not have read what I posted???

----------

