Move data rows from multiple sheets to a master sheet in another workbook

**Nick_WBA** · 09-13-2012, 04:12 PM

Hi, new to the forum so please forgive me if I post anything too ambiguous.

I have a basic to intermediate knowledge of writing VBA, having no luck with this one though..

I have a number of workbooks with named with different work colleagues and dates to differentiate between weeks of the year
each workbook therefore contains 5 visible tabs.. Monday through Friday.

each worksheet has a the same table laid upon it, the top 190 rows are used up by formula's and look ups etc to allow the rest of the table to function correctly
line 191 of each sheet is a header and then there are 50 rows within the table for users to input specific data.

What i want this macro to do it when they click the save button (a VBA button on the sheet itself) all data within the data table that has not yet been copied, to be copied to another workbook, lets say named master. i presume this means I will need to have some sort of indicator for the code to ascertain which rows have already been copied which is why I have left column B empty. then each time the user saves the process will then save any recently edited rows to the existing master sheet.

I need for all users (currently 20ish) data to be added to the same master workbook without overwriting any data that already exists.

The full name of the user in the sheet will be in cell B1, i need this to be added to column A of the master sheet to be able to identify which users data is which when I come to report

- I do not want to bring any rows of the table that have not been populated
-needs to work for excel 2003

any help would be very very much appreciated.

Thanks in advance

Nick

**arlu1201** · 09-14-2012, 06:22 AM

This can be done. Do you have a sample file that you can upload?

To Attach a File:

1. Click on Go Advanced
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.

**Nick_WBA** · 09-14-2012, 08:38 AM

FW BLANK.xls

Hi Arlette,

thanks for replying, I have uploaded a blank sample, if you have any problems with the protection on these sheets the unlock is nickwba1987

many thanks

Nick

**arlu1201** · 09-14-2012, 10:24 AM

So you have multiple such files with 5/6 tabs each right? Are these stored in a folder and the macro needs to open each one to populate the master file?

What indicator will be put in column B to identify the already copied rows?

Should only the non-indicated rows be copied to the master file and then the indicator put in for each row?

**Nick_WBA** · 09-14-2012, 10:43 AM

Hi

I was hoping that each workbook could have a macro in that would copy all previously uncopied rows as identified by column B for instance to the master sheet. it would not make to much of a difference if the master sheet had one macro that would pull all the data though

each workbook will have 5 tabs visible monday through friday these need to have all information in the table starting at row 190 copied across.

the indicator can be anything really, a small check mark maybe or a X simbol, just something to avoid duplicates being moved across.

and yes non indicated rows should be copied and the indicated that they have.

Thanks for you continued help.

Nick

**arlu1201** · 09-14-2012, 10:46 AM

There is another way of doing this as well. You can clear the master file each time you run the macro and then fill up data right from row 192 onwards. So in that case, no duplicates will arise and you wont have to indicate whether the row is copied or not.

Does this sound fine or do you want to go with the indicator method?

**Nick_WBA** · 09-14-2012, 10:57 AM

If that is much easier for you to do for me then I could work with that and just be sure to update it, I just feel I have less chance of losing data, and speed of running the macro when I want to run a quick report with the indicator and copying lines ad hoc.

thanks

Nick

**arlu1201** · 09-14-2012, 02:18 PM

I could provide you either codes. You just tell me what will make it easier for you.

**Nick_WBA** · 09-14-2012, 03:28 PM

Could I please have the code that
Moves from each sheet and checks the line as it does so then please. I think that will help visually to ensure nothing is missed.

I also need the code to copy from the protected cells.

Thanks again for your help

Nick