Combine all sheets into one

ssblue
ssblue used Ask the Experts™
on
Excel - I received assistance on some code that updates a master sheet based on several other sheets.  
I would like to be able to use different workbooks instead of different tabs.
Example: Have 4 or 5 work books that update a Master file. The master file would contain the Change log
CombineIntoOne.xlsm
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Roy CoxGroup Finance Manager

Commented:
Are all the files in one folder?
ssblueCoordinator

Author

Commented:
yes
Roy CoxGroup Finance Manager

Commented:
Is the e\ample attached based on a master workbook. That is, will the other workbooks be the same layout  - sheets for various people and a change log. Each sheet will then be imported to the master workbook and added to the relevant sheet there.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

ssblueCoordinator

Author

Commented:
Yes, all sheets will be laid out the same and reside in the same folder with the Master showing the latest revision.
Roy CoxGroup Finance Manager

Commented:
I'll have a look at adapting some code for you.
ssblueCoordinator

Author

Commented:
Thanks!!
Roy CoxGroup Finance Manager

Commented:
I haven't tested this so it might need some tweaking. Check it out on sme dummy workbooks and let me know. I'll be around tomorrow if you need changes
CombineIntoOne.xlsm
Professor JMicrosoft Excel Expert
Top Expert 2014

Commented:
Hi ssblue

you can use http://www.rondebruin.nl/win/addins/rdbmerge.htm    Free Excel add-in RDBMerge

rdbmerge.png
ssblueCoordinator

Author

Commented:
Professor J, thanks for the info however I can't use an Excel add-in at work. :(

Roy, I'm not sure I explained this correctly.  The example I posted works fine. Each tab is updated to the Master tab.  However what I would like to do Is have Five (or more) separate files. One for each person and One Master file. All files would reside in the same location. Each person would open their file to work on and the Master file would automatically be updated.
The file you posted seems to me similar to the original one I posted, If I am missing something here please let me know. Sorry for the confusion.
Group Finance Manager
Commented:
I think you would need to have some code in the before close event of each workbook. However, I think the best way would be to have one workbook, but access to individual sheets limited by a password for each user. Unless  they need access at the same time.
ssblueCoordinator

Author

Commented:
That's what I was afraid of. What we have now is working but I was hoping for something that might eliminate some confusion for the users. Sometime they click on the wrong tab. : (
Thanks for the help and advice. I think I will close this question if no one objects.
ssblueCoordinator

Author

Commented:
Both offered insight and helpful suggestions but nothing that would work in my setting.
Roy CoxGroup Finance Manager

Commented:
How do they click on the wrong tab? Surely they have one tab in their own workbook.

Also, the problem I see is how do you only import/export new items, are existing items deleted after saving?
ssblueCoordinator

Author

Commented:
So there is one workbook with 5 tabs, Master tab and tabs with their names. They will click on someone else's tab for whatever reason - I don't know .
Each sheet is exactly alike with all the information needed except for the columns where updates are to be made. So each person is responsible for their own rows based some of the information in the sheet. No new rows should be added once the updates begin. If there is a need for some new rows to be added then they are added to all the sheets.
Roy CoxGroup Finance Manager

Commented:
I have a userform that requires the user to enter their name and password to access their worksheet. Take a look, if it helps I can add it to your workbook
PASSWORD-FORM-7.xlsm

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial