Share this content
0
85062

How do I combine worksheets in Excel?

How do I combine worksheets in Excel?

Didn't find your answer?

Search AccountingWEB

I have an Excel workbook containing 50 worksheets. I need to amalgamate them all into one big worksheet.

Each worksheet contains the same column headings and, say, 20 records. Does anyone know how to pull them into one big worksheet of 20 x 50 = 1000 records?

It would save me an awful lot of cutting and pasting. Thanks in advance.

David Carter

Replies

Please login or register to join the discussion.

avatar
08th Feb 2005 00:03

More
Thanks Stewart, it's in the post.

Dennis and John, your solution would work to combine numbers, I think, but my 50 worksheets are all text.

Thanks (0)
avatar
07th Feb 2005 10:52

re: Almost works
Hmm... David - you mentioned those horrible letters... A-O-L...

Actually, it does sound as though something's gone awry, but "taking 15 minutes" to cut and paste may just mean it's too big even for the script to cope with!!! If you copy and paste the whole of the error page, then that should give me an idea as to the problem.

If it's not too confidential, you can also email the spreadsheet to me and I'll get it working for you... or you can paste in dummy values into all the cells if you want to "hide" the figures.

...but back to AOL - the output of the script is a pure XML file which should just save as without a problem... on Internet Explorer it shows as XML fine, but it is possible that the AOL browser you are using doesn't support receiving XML as type text/html...

Stewart Twynham
[email protected]

Thanks (0)
avatar
06th Feb 2005 12:01

Stewart - it almost works
Thanks very much for your ideas, everybody. Apologies for not replying sooner but I've been a bit bogged down this week.

Clearly there's no simple answer on this and one day I am going to have to master VB. Does anyone out there know of any idiot level tutorial to get me started?

Stewart, I copied my spreadsheet as XML, then loaded it into Notepad. Then Select All and Paste into your box. It took about 15 minutes but it got there. Then I pressed Submit and everything seemed to be working for a minute or two.

I'm assuming that, after processing, the finished big worksheet should have been displayed in your box and I would just use AOL's File -Save As to copy to my own drive C.

However, just when it seemed to have created the final worksheet, your screen disappeared and I got the message "Page Cannot be Displayed".

Any idea where I went wrong? And what does Reset mean?

Thanks for all the help. In the meantime I'll have a go at Alastair's macro recorder.

David

Thanks (0)
avatar
07th Feb 2005 09:10

how to master vba
to master vba requires patience, and lots of it!

there are lots of books - but didn't use one so can't recommend - but did find a "vb idiots guide" useful - can't remember which one, and it has long since gone on to pastures new. I do recall that it was yellow and had lots of annoying cartoons.

found the vba help useful - lots of examples and includes code that you can copy and paste.

found the macro recorder useful as a starting point.

found some good articles on the microsoft website, and some via google searches.

was lucky enough to work for several years with a real expert, who was a constant source of advice.

still find the syntax hard going.

Thanks (0)
avatar
03rd Feb 2005 13:23

VBA to Amalgamate Worksheets
Hi there,

If you select Tools > Macro > Visual Basic Editor and paste this code in, then hit the play button you'll end up with all of your data on one tab.

Notes:

You need to insert a new worksheet at the *front* of the workbook to receive the data.

This sheet should have values in cells B1 & B2 - it doesn't matter what you put in.

Replace n below with the number of worksheets in your workbook.

Caveat:

Assumes you have a header row on each sheet. If you have no headers replace "R2C1" with "R1C1" below.

Sub amalgamate_tabs()


Dim intRowNumber, intNewRowNumber, intSheetNumber As Integer


intSheetNumber = 2

For i = 1 To n 'The number of sheets with data

'Activate the data sheet
ActiveWorkbook.Worksheets(intSheetNumber).Activate

'Goto cell a1
Application.Goto Reference:="R2C1" 'Replace this if you have no header row

'Select all of the data on the sheet and copy
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Copy

'Move to the receiving worksheet
ActiveWorkbook.Worksheets(1).Activate

'Goto the first data cell in the receiving sheet
Application.Goto Reference:="R1C2"

'Find the number of the last completed row in the receiving sheet
'and assign this to intRowNumber
intRowNumber = Range("b1").End(xlDown).Row

'Increase intRowNumber by 1 to reference the first blank row
intRowNumber = intRowNumber + 1

'Move to the first blank row
Range("b" & intRowNumber).Select

'Paste in the data
ActiveSheet.Paste



'Add in the sheet name to allow tracking back in case of problems
Range("a" & intRowNumber).Select
Range("a" & intRowNumber).Value = ActiveWorkbook.Worksheets(intSheetNumber).Name

'Autofill to populate the sheet name down the range

'Find the new number of rows in the spreadsheet
intNewRowNumber = Range("b2").End(xlDown).Row

'Check that there is more than 1 row to fill
If intRowNumber - intNewRowNumber <> 0 Then

'If there is then autofill the range
Range("a" & intRowNumber).Select
Selection.AutoFill Destination:=Range("a" & intRowNumber & ":" & "a" & intNewRowNumber)

Else

'No need to fill
End If

'Increase the counter to reference the next sheet
intSheetNumber = intSheetNumber + 1

Next i

ActiveWorkbook.Worksheets(1).Name = "All Data"

End Sub

Hope that helps, Iain

Thanks (0)
avatar
01st Feb 2005 11:01

XL Concatenator
Okay, not perfect - but I thought this up last night whilst settling my six week old.

1. Go to...

http://www.bawden-quinn.co.uk/clientapps/xlconcat

2. Copy and paste the whole of the XML spreadsheet saved below into the box and press submit.

3. Resulting page will contain new global spreadsheet. Rows are calculated automatically, and regular expressions used to trim out the <row> tags.

Bugs: (a) cannot cope with different numbers of columns, first worksheet MUST have the most (or equal number of) columns of all sheets.

(b) First sheet becomes the placeholder for all sheets.

Enjoy!
Stewart
[email protected]

Thanks (0)
avatar
By Abacjm
29th Jan 2005 04:06

Linking Spreadsheets
David
I am no expert on the various shortcuts on Excel, but I found that this works.
I creat a Master or Lead Work-sheet, using the same column headings as the other w/sheets.name it Master and place it at the beginning of your worksheet labels.
Then for the value of say RCA4 make that = the total C4 in Sheet 1. Copy right the number of cells. Do similar for each w/sheet and it will bring the total of each w/sheet to your master.
Remember to inlcued a Proof zero column at the end of the Master (and prob at the nd of each of the w/sheets. This saves a lot of auditing later on).

Someone else may have a whizz formula using VBA or some other ploy within Excel that us ordinary guys just havent got the time or the brainpower to understand!!
Good luck.

Thanks (0)
avatar
31st Jan 2005 09:32

depends
can't think of any clever key strokes, but you could make use of the macro recorder to assist. you will still have to select each sheet (unless you want to get clever with vba), and run the macro, but it looks more complex than it is.

Depends on the size of the data in each sheet. but if you can calculate the maximum size then you could try this:

create a new sheet to capture the results.

select the first sheet with data.

select tools, macro, record new macro - it will open the record macro toolbox.

assume that the top of the data including headings is cell a1 (including headings) and the bottom of the largest is f250.

select the top left hand corner of the data range - a2 - , and then select the whole of the range down to f250.

click on copy.

select the cell a2 in the new result sheet.

select edit, and paste special.

select a2, and then press the keys {end}{down arrow}, one at a time

then click on the button to stop macro recording.

then press the keys {alt}{F11} together - to invoke the vba editor.

in the left hand window you will see an explorer like view. double click on the modules folder, and then double click on module1.

you should see in the right hand window some vba code, enclosed by:

sub macro1()

end sub

create a new row before the end sub statement and type in the following line:

ActiveCell.Offset(1, 0).Select

job nearly done.


To run the macro, select asheet, and select tools, macro, macros, which will open up the run macro window. select macro1 and click on run. This should copy the data only one below the other into the results sheet. The assumption is that each sheet has the same column headings, and the headings are all in the same place on each sheet. And it will copy a standard range fromeach sheet, which in some cases will include blank rows.

So to complete the job, create a blank column to the left of the results data in the results sheet, and select the range to the left of the data, and use edit, fill, series, to create a column of numbers in ascending orders. The select the whole range, including the numbers, and sort on the first data column. Then delete all the blank rows, and then sort again but on the numbers column, to put the data back into order.

job done. hope this helps. good luck.

Thanks (0)
avatar
31st Jan 2005 19:45

Export as XML
Hi David,

Bit fiddly, and it's best using XSLT if you want to automate it... but if it's just a one-off try this method.

1. Save the spreadsheet as an XML file (Save As, XML spreadsheet).

2. Open the XML file in Notepad and manually remove the sections that divide the worksheets e.g.:

</Table>

...lots of rubbish...

<Table ss:ExpandedColumnCount="3" ss:ExpandedRowCount="20" x:FullColumns="1"
x:FullRows="1">

...but don't remove the very last </Table>

3. Then set the number of rows in the first table tag to a number larger than total the number of rows e.g. ss:ExpandedRowCount="1000"

<Table ss:ExpandedColumnCount="3" ss:ExpandedRowCount="1000" x:FullColumns="1"
x:FullRows="1">

4. Save the file and open in XL - bingo, one big worksheet.

Stewart Twynham
[email protected]

Thanks (0)
avatar
07th Feb 2005 17:15

Excel consolidation of data
there are a couple of inbuilt Excel functions for this either- Data -consolidate
or Data - Pivot tables - multiple consolidation ranges

both work in a similar manner -- and are a bit fiddly to set up .

the online Excel Help is- in these cases quite usefull!

Because of the number of worksheets you MAy have to carry out the process in 2 or more steps- e.g. combine say 2 * 20 and 1*10 sheets and then combine these 3 summary sheets

Thanks (0)
avatar
11th Dec 2009 13:29

Merge Excel Workbooks

I realise the original question relates to merging worksheets, not workbooks.  If in fact there is a need to merge excel files or workbooks,  I ran into a handy utility that will merge xls or csv files into one master file.  The program, bulk file merger, merge over 240 file in a matter of minutes.  What was over an hour now takes minutes.

Thanks (0)
avatar
16th Jan 2010 16:05

Trying to get data from two workbooks onto the same sheet

Hi,

I am trying to do similar as here (with some basic VB code), in trying to copy data from two workbooks onto the same sheet, prior to processing the data.  Unfortunately the second file doesn't have all the columns the first does.  So file 1: A, B, C, D and E, while file 2 has: C, D and E.  I have a macro that installs and processes file 1 ok, but if I try and run it on file 2 it returns incorrect data (having imported and processed the data as A, B, C rather than C, D, E).

If anyone thinks this code may be of use or how to improve it let me know.

Accounting 4417;  do you this software would be useful for running two files (rather than 500+)

Thanks

Thanks (0)
avatar
19th Apr 2011 22:05

The opposite of combining worksheets in excel

I found a cool app that does the exact opposite of combining worksheets in Excel.  It combines multiple rows in excel and extracts them into one spreadsheet and then can email this to people.  Designed for sales commissions but believe it might be useful for other tasks as well (i.e. breaking down aggregated AP or AR worksheets and sending the right parts to the right people).  Check it out here:  www.oneclickcommissions.com/excel-worksheets.html

Thanks (0)
avatar
03rd Jan 2015 01:32

Merging spreadsheets

Hi everyone,

I found a piece of software that combines multiple spreadsheets into one with just a few clicks. It saved me a ton of time this week. Available for download from a site called Sourceforge https://sourceforge.net/projects/mergewithoutmacros/ They also have a wiki on how to use the software, in case that is an issue for anyone.

Thanks (1)
Share this content