Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Thread Tools Display Modes
Old 07-15-2011, 04:02 AM
Mahen Mahen is offline merge excel sheets Windows XP merge excel sheets Office 2007
merge excel sheets
Join Date: Apr 2011
Posts: 6
Mahen is on a distinguished road
Default merge excel sheets

Is it possible to merge several sheets information into one sheet. All sheet have the same format of information. But they are in different sheet. eg. sheets called age 4, age 5 , age 6, age on. Each sheet contains
column 1 - Ref No.
column 2 - Name
column 3 - points gained.

Need to have all information in one sheet, so I can sort out which ever order I want. I want to avoid copy and paste, it takes ages.
Can anyone help please?

- Mahen
Reply With Quote
Old 07-26-2011, 11:35 AM
OTPM OTPM is offline merge excel sheets Windows 7 32bit merge excel sheets Office 2010 32bit
Join Date: Apr 2011
Location: West Midlands
Posts: 981
OTPM is on a distinguished road

Hi Mahen
Found this post on the internet which seems to work. I have posted as is:

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.
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.
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

'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

'Move to the receiving worksheet

'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

'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)


'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
Reply With Quote
Old 08-03-2011, 12:21 AM
Catalin.B Catalin.B is offline merge excel sheets Windows Vista merge excel sheets Office 2007
Join Date: May 2011
Location: Iaşi, Romānia
Posts: 386
Catalin.B is on a distinguished road

maybe you can use this sample workbook i made for another user, in which you can select the range for every worksheet you want to copy.
Attached Files
File Type: xlsm copy sheets.xlsm (20.7 KB, 43 views)
Reply With Quote

Thread Tools
Display Modes

Similar Threads
Thread Thread Starter Forum Replies Last Post
merge excel sheets Merge from 2 excel worksheets apolloman Mail Merge 2 06-02-2011 04:16 AM
merge excel sheets How do I merge data from one sheet in a workbook out into multiple sheets nolesca Excel 4 06-07-2010 08:13 AM
Excel 2007 doesn't print color graphics on multiple sheets mwoolf Excel 1 06-19-2009 02:34 AM
unwanted sheets on opening excel msofficeman Excel 3 02-24-2009 03:13 PM
merge excel sheets Movement slide with excel sheets majed PowerPoint 4 01-17-2009 04:24 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 03:43 PM.

Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2023, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2023 DragonByte Technologies Ltd. is not affiliated with Microsoft