Integrating data from multiple copies of same worksheet

[thedonald627]

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.

 

[HalfAce]

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]

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]

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]

Thanks for your patience. Yes, they are all working in separate workbooks. Separate copies of the same workbook.

 

[HalfAce]

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.