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

Thanks Howard, Donald and Peter,
I'll keep your suggestions in mind for other situations.
The person needs them all on one worksheet tab to compile a pivot table
from the data.
So in this instance she has done the "brute force" thing.
Good to know of the feature in 2003 version, we are using 2000 version
here.
 
regards,
matilda

>>> Howard.Silcock@xxxxxxxxxxx 12/04/07 5:04 pm >>>


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.


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

  

----------------------------------------------------------- This
message has been scanned by MailSweeper.
-----------------------------------------------------------

Other related posts: