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.

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.

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.

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.

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.

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.