MERGING SHEETS

[atuljadhavne]

Dear Expert

I have file having multiple sheets and all sheet having same format
i want to copy particular row from all the sheets which is starting from Row No. 15 to till the data having in A column,
e.g in below table i want to copy entire row from 15 to 18 and copy in single file from every sheet below, after copy this 4 row macro jump to next sheet and copy the row and paste below this row and cont. till last sheet,
in short i want entire row starting from 15 to till data in A column it may be 20 or 100 row or only 2 row

A B .......
15 12345 12
16 23563 25
17 12524 32
18 5252 15
19
20

using excel 2007, file name tool.xls store at E:\May 12\

[Tinbendr]

Maybe you can find an answer here. (http://www.rondebruin.nl/tips.htm)

[Teeroy]

Try the following:

Dim firstrow As Integer
Dim lastrow As Long
Dim dest As Worksheet
Dim sht As Worksheet
Dim destcell As Range
Sub collate_data()
firstrow = 15
'Insert new sheet as destination for data
Set dest = Sheets.Add
dest.Name = "Master"
'Set range for copies to go to leaving a row for a header
Set destcell = dest.Range("A2")

For Each sht In Sheets
If sht.Name <> dest.Name Then
'find bottom cell in col A
lastrow = sht.Range("A" & Rows.Count).End(xlUp).Row
'copy data range to destcell
sht.Rows(firstrow & ":" & lastrow).Copy Destination:=destcell
'set new position of destcell
Set destcell = destcell.Offset(lastrow - firstrow, 0)
End If
Next sht
End Sub