how to merge execel sheets in excel 2003
 

i have 30 sheets in one excel book. i want to merge all excel sheet in 1
sheet. how i can do this with out copy & paste.

how to merge execel sheets in excel 2003
 

Hi,please refer the below link by Ron de Bruin

http://www.rondebruin.nl/copy2.htm

If this post helps click Yes
---------------
Jacob Skaria


"TEJAS SHAH" wrote:

i have 30 sheets in one excel book. i want to merge all excel sheet in 1
sheet. how i can do this with out copy & paste.

how to merge execel sheets in excel 2003
 

Hi,
Look into Ron Web

http://www.rondebruin.nl/copy2.htm

"TEJAS SHAH" wrote:

i have 30 sheets in one excel book. i want to merge all excel sheet in 1
sheet. how i can do this with out copy & paste.

how to merge execel sheets in excel 2003
 

Thanks for your prompt response. but i am not understanding the macros.

i am having 1 workbook

sheet 1 15cols & 20rows (1strow is header)
sheet 2 15cols & 50rows (1strow is header)
sheet 3 15cols & 15rows (1strow is header)

now i want in sheet 4
sheet 1 15cols & 20rows (1strow is header)
start from row no 21
sheet 2 15cols & 49rows
start from row no 69
sheet 3 15cols & 14rows

thanks in advance



"Jacob Skaria" wrote:

Hi,please refer the below link by Ron de Bruin

http://www.rondebruin.nl/copy2.htm

If this post helps click Yes
---------------
Jacob Skaria


"TEJAS SHAH" wrote:

i have 30 sheets in one excel book. i want to merge all excel sheet in 1
sheet. how i can do this with out copy & paste.

how to merge execel sheets in excel 2003
 

Hi
Here is the code

Sub CopyRangeFromMultiWorksheets()
Dim sh As Worksheet
Dim DestSh As Worksheet
Dim Last As Long
Dim CopyRng As Range

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

'Delete the sheet "RDBMergeSheet" if it exist
Application.DisplayAlerts = False
On Error Resume Next
ActiveWorkbook.Worksheets("RDBMergeSheet").Delete
On Error GoTo 0
Application.DisplayAlerts = True

'Add a worksheet with the name "RDBMergeSheet"
Set DestSh = ActiveWorkbook.Worksheets.Add
DestSh.Name = "RDBMergeSheet"

'loop through all worksheets and copy the data to the DestSh
For Each sh In ActiveWorkbook.Worksheets
If sh.Name < DestSh.Name Then

'Find the last row with data on the DestSh
Last = LastRow(DestSh)

'Fill in the range that you want to copy
Set CopyRng = sh.Range("A1:O1")

'Test if there enough rows in the DestSh to copy all the data
If Last + CopyRng.Rows.Count DestSh.Rows.Count Then
MsgBox "There are not enough rows in the Destsh"
GoTo ExitTheSub
End If

'This example copies values/formats, if you only want to copy the
'values or want to copy everything look at the example below
this macro
CopyRng.Copy
With DestSh.Cells(Last + 1, "A")
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteFormats
Application.CutCopyMode = False
End With

'Optional: This will copy the sheet name in the P column
DestSh.Cells(Last + 1, "P").Resize(CopyRng.Rows.Count).Value =
sh.Name

End If
Next

ExitTheSub:

Application.Goto DestSh.Cells(1)

'AutoFit the column width in the DestSh sheet
DestSh.Columns.AutoFit

With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub


Function LastRow(sh As Worksheet)
On Error Resume Next
LastRow = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
End Function


Function LastCol(sh As Worksheet)
On Error Resume Next
LastCol = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Column
On Error GoTo 0
End Function



"TEJAS SHAH" wrote:

Thanks for your prompt response. but i am not understanding the macros.

i am having 1 workbook

sheet 1 15cols & 20rows (1strow is header)
sheet 2 15cols & 50rows (1strow is header)
sheet 3 15cols & 15rows (1strow is header)

now i want in sheet 4
sheet 1 15cols & 20rows (1strow is header)
start from row no 21
sheet 2 15cols & 49rows
start from row no 69
sheet 3 15cols & 14rows

thanks in advance



"Jacob Skaria" wrote:

Hi,please refer the below link by Ron de Bruin

http://www.rondebruin.nl/copy2.htm

If this post helps click Yes
---------------
Jacob Skaria


"TEJAS SHAH" wrote:

i have 30 sheets in one excel book. i want to merge all excel sheet in 1
sheet. how i can do this with out copy & paste.

how to merge execel sheets in excel 2003
 

Hi,

You may try the procedure mentioned here. This procedure will work for
consolidating data from worksheets as well.

http://datapigtechnologies.com/blog/?cat=26

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"TEJAS SHAH" wrote in message
...
i have 30 sheets in one excel book. i want to merge all excel sheet in 1
sheet. how i can do this with out copy & paste.