Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 07-15-2011, 04:02 AM
Mahen Mahen is offline merge excel sheets Windows XP merge excel sheets Office 2007
Novice
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 7......so 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
  #2  
Old 07-26-2011, 11:35 AM
OTPM OTPM is offline merge excel sheets Windows 7 32bit merge excel sheets Office 2010 32bit
Expert
 
Join Date: Apr 2011
Location: West Midlands
Posts: 981
OTPM is on a distinguished road
Default

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

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
Reply

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 12:39 AM.


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