atw: Re: Merging Excel workbooks [SEC=UNCLASSIFIED]

Hi Matilda

Wouldn't it be good enough to make the 35 spreadsheets into separate 
worksheets in the same workbook?  You can then easily include formulas 
that sum or consolidate data across the different worksheets.  Unless the 
original spreadsheets have very similar structure it would be hard to 
format the rows and columns to accommodate them all on one worksheet 
anyway.  It's generally better, from a stylistic point of view, to 
organise data into separate sheets in a workbook than to put everything 
onto a single sheet. 

If you open a new workbook you can drag each of the source spreadsheets 
into it one by one. With the new workbook and one of the source 
spreadsheets open, select Arrange on the Window menu and tile the two 
horizontally (or put one on a second monitor if you have a dual-screen 
set-up) , then hold down the Ctrl key and click and drag the tab at the 
bottom of the source spreadsheet (by default labelled Sheet1) into the new 
workbook and plonk it down to the right of the rightmost tab at the bottom 
of the screen. This copies the original spreadsheet in as a new worksheet. 
 Then close the source spreadsheet, open another one and repeat, until you 
have 35 new worksheets in your new workbook.  (You can now delete the 
worksheets that were there originally if you want (activate each one then 
select Edit > Delete Sheet)).

If you really want to merge all the spreadsheets into a single sheet, the 
only way I can think of is by brute force: select the entire area occupied 
by data on a source spreadsheet, select Edit > Copy, then click on the 
first cell in the first empty row of the target worksheet and do Edit > 
Paste. You should be able to do that even if the source area has thousands 
of rows and/or columns. Just save the target worksheet after pasting each 
new block of data in.  It may be easier to put all the source speadsheets 
into a single workbook, as above, before you start the copying.

Feel free to email me off-list if any of this is unclear. 

Howard
Technical Writer
Zare Pty Ltd





"MATILDA REICH" <MATILDA.REICH@xxxxxxxxxxxxxxxxxx> 
Sent by: austechwriter-bounce@xxxxxxxxxxxxx
12/04/2007 03:53 PM
Please respond to
austechwriter@xxxxxxxxxxxxx


To
<austechwriter@xxxxxxxxxxxxx>
cc

Subject
atw: Merging Excel workbooks
Topic





TRIM File Ref










Hello all,
I have an Excel problem. Someone at work mistook me for an Excel guru. 
They want to merge 35 spreadsheets into one excel document.
As a combined document the total rows would come to between 3,000-4,000 - 
well within Excel's capacity. 
Excel does not appear to have the Insert File option Word does which 
allows you to append files. 
Searched the Help but could only find "Troubleshooting merging workbooks" 
which is no help.
 
regards,
Matilda
 
 
 

-----------------------------------------------------------

Sydney Water - Winner of the 2006 Stockholm Industry Water Award

-----------------------------------------------------------

 

NOTICE: This email is confidential. If you are not the nominated 

recipient, please immediately delete this email, destroy all copies, and 

inform the sender. Sydney Water Corporation (Sydney Water) 

prohibits the unauthorised copying or distribution of this email. This 

email does not necessarily express the views of Sydney Water. 

Sydney Water does not warrant nor guarantee that this email 

communication is free from errors, virus, interception or interference.

-----------------------------------------------------------

Other related posts: