Integrating data from multiple copies of same worksheet

thedonald627

New Member
Joined
Oct 6, 2006
Messages
3
I have developed an excel database worksheet. Multiple copies will be used to allow several people to input data simultaneously. Please tell me how I can merge or integrate the data from these various copies into one master worksheet.
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,454
Hello thedonald627, welcome to the board.
I have developed an excel database worksheet. Multiple copies will be used to allow several people to input data simultaneously
Are all these users entering data to separate sheets within the same workbook, or
are they all in separate workbooks?

Will there be duplicate data between some of these sheets that you want only one
instance of, or do you want it all 'as is'?

Are you asking how to do it manually, or are you looking for some vba code to do it
for you?
 

thedonald627

New Member
Joined
Oct 6, 2006
Messages
3
Integrating Data

I am an extremely unsophisticated user of Excel. I don't know the difference between a worksheet and a workbook. I don't know what VBA means.

I can't put my question much more simply than I have. I've got several people entering data into copies of a spreadsheet I created. I just need to know how to combine all that information in a master copy without duplication and without destroying any data.
 

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,454
OK, no problem. We'll take it slow. :wink:
When you save & close (or open) a file, that's the workbook.
Within the workbook are several (or any number of) worksheets. You'll see the sheet names in little tabs on the lower left part of your screen.
(Unless they've been renamed they'll be "Sheet1", "Sheet2", Sheet3", etc.)

What we need to know is if all your users are entering their data into separate workbooks (files), or if they are all on their own worksheet within the same workbook.

I suspect they are all in their own workbooks but need to know for sure.

As for the vba, we'll get to that in a bit. All that is, is the code behind a button that you'll click to automatically perform some function(s).

Once I understand exactly what you're working with I'll walk you through how to do it manually. Then we should be able to automate some or all of it for you.
 

thedonald627

New Member
Joined
Oct 6, 2006
Messages
3
Thanks for your patience. Yes, they are all working in separate workbooks. Separate copies of the same workbook.
 

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,454
OK, let's try this.
I assume you have all these workbooks back on one computer and in the same folder. (yes?)
Open the workbook in which you want to put all this data.
From the menu, choose Tools > Macro > Record new macro... (and then OK)
This will do two things. One, it will (or at least should) open up a little window that says "Stop" and has a little blue button on it. (This is to stop recording when you're all done. Just ignore it for now.) The other thing it'll do is record every action you take from now until the time you click the little blue (stop) button.

Next, open up one of the files you want to copy data from, select the sheet you want to copy from and copy the entire range you want to copy.

Then, select the workbook you want to paste the data to, select the range to paste to and paste.

Close the workbook you copied from and open up the next workbook you want to copy from and repeat the process.

Keep repeating this untill all the workbooks have been copied from and pasted into your master worksheet.

Now you can click the small blue stop button to stop recording your actions.

Next, press Alt+F8. You should see in the window that opens the name of the macro you just recorded. (Unless you named it something different it will by default be named "Macro1") Select that in the white area of the window and click on the Edit button. This will show you the code of your new macro. Copy all of that code and paste it here on the board so I can see what you're doing. Then I can help clean up the code and make any changes you want to make to it.

Good luck and by all means post back if you have any questions.
 

Forum statistics

Threads
1,141,924
Messages
5,709,372
Members
421,633
Latest member
Ubergribbler

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top