We help IT Professionals succeed at work.

Merging Multiple Excel Worksheets into Master

Medium Priority
429 Views
Last Modified: 2012-05-12
Hi,

I know this is easy but can't get this working...

We have multiple Excel 2007 worksheets in separate files, e.g. test file 1, test file 2, etc.

I want to merge all the data from sheet 1 of each file into a new Excel 2007 workbook, sheet 1.

I know this is a module and want to schedule this to run automatically and only update fields with modified and/or new data.

Thank You
Comment
Watch Question

Sajid Shaik MSystem Admin
CERTIFIED EXPERT

Commented:

Author

Commented:
Thanks but linking is not what I need as additional info will continue to be added to the sheets in the files. I need to be able to run a macro automatically that will scan all existing workbooks and sheets for updated info and then place the newly modified and/or new data into one file called master.

Then, my ultimate goal is to create a dashboard from the master file and display in SharePoint 2007.
Sajid Shaik MSystem Admin
CERTIFIED EXPERT

Commented:
CERTIFIED EXPERT

Commented:
hi,

I'm not sure if this will help or not (& it doesn't answer all your questions), but since you are consolidating info from multiple files into a single file Rin De Bruin's free addin may help. It is quite flexible & easy to use and can be sourced from his site: http://www.rondebruin.nl/merge.htm

hth
Rob
CERTIFIED EXPERT
Commented:
hi gdamiani123,

(ooopps! my last post should state "Ron De Bruin" instead of "Rin".)

Have you considered the use of dynamic named ranges in your source files?
These could be set up to expand when new data is entered.

Is it possible for you to upload two sample files (ie a one that contains the source data & a "master file")?
Please remove any confidential data but provide files with the same structure/layout as your real files.

Another option for your dashboard may involve the use of excel's inbuilt "camera tool". If you haven't heard of this, see what you find with a Google search for ("Charley Kyd" "Excel dashboards" "camera tool"). Here are some of the results I found that may be worth reading:

http://excel-user.blogspot.com/2010/03/excel-camera-tool.html
http://exceluser.com/blog/341/excel-dashboard-sample-advice-about-using-excels-camera-tool.html
http://www.excelcharts.com/blog/excel-dashboards-charley-exceluser-kyd/

Please note the camera tool can be a little buggy as mentioned in the following link. An example I remember from a few years ago was when I had problems with it "holding on to information/images" from files that no longer exist, instead of generating an error.
http://exceluser.com/blog/613/a-new-excel-2010-camera-tool-bug-and-a-workaround.html

hth
Rob

Author

Commented:
This was excellent info; I loaded the add in and that worked with no issues and now have enabled the camera tool. I'll begin creating the dashboards in Excel and see how I can get them to work in SharePoint 2007.
CERTIFIED EXPERT

Commented:
Thanks for the Grade & good luck with your dashboards :-)

Explore More ContentExplore courses, solutions, and other research materials related to this topic.