Consolidating data on different sheets

[ashish514]

I want to bring all the data in different sheets(same file) in a single sheet. The data is in the same format shown below in all the sheets.

Description	Drawing no.	EDP	Unit Price

<tbody>
</tbody>

I need to have a single sheet having the same column headings at the top of that sheet. On searching, I read something about consolidate tool, but having a little difficulty in using it. Kindly help.

 

[ashish koul]

try this see if it helps

Option Explicit

Sub merge1()
Dim i As Long, z As Long
' add new sheet at last and name it merge
Sheets.Add After:=Sheets(Sheets.Count)
Sheets(Sheets.Count).Name = "Merge"
For i = 1 To Sheets.Count - 1
' add headers from first sheet
If i = 1 Then
' find last filled row in the sheet
z = Sheets(i).Range("a1048576").End(xlUp).Row
' paste the data on the merge sheet
Sheets(i).Rows("1:" & z).Copy Destination:=Sheets("Merge").Range("a1")
Else
' find last filled row in the sheet
z = Sheets(i).Range("a1048576").End(xlUp).Row
' paste the data on the merge sheet
Sheets(i).Rows("2:" & z).Copy Destination:=Sheets("Merge").Range("a" & Sheets("Merge").Range("a1048576").End(xlUp).Row + 1)
End If
Next
End Sub