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!

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

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.

Originally Posted by mrice

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

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.

You might need a line like

Please Login or Register  to view this content.

VBA Magic, welcome to the forum

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.

Hi VBA Magic....

Originally Posted by FDibbins

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

Originally Posted by Pete_UK

Alan,

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

Pete

OK, found it, thanks.
Alan

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

Hi Himanshuktw
Welcome to the Forum

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

Originally Posted by Himanshuktw

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

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.

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

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.

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.

Originally Posted by kevin65811

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?

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

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

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