merging different worksheets in a workbook to one sheet

[a.pontes]

Is there a macro that I can take a work book that has 8 seperate worksheets
of data, and put all the data on one worksheet?. I need to sort all the data
on one sheet. Currently I cut and paste the data to the one sheet.
Any help would be appreciated.
Thanks,
AP

 

[Mohamed Shafiee]

Hi there...

This will work.

Shafiee.

Sub MakeOneSheet()
Dim strpasterange As String
Dim wbk As Excel.Workbook
Dim awbk As Excel.Workbook
Set awbk = ActiveWorkbook
Set wbk = Application.Workbooks.Add
For i = 1 To ActiveWorkbook.Sheets.Count
strpasterange = "'[" & wbk.Name & "]" & wbk.Sheets(1).Name & "'!A" &
wbk.Sheets(1).UsedRange.Rows.Count + 1
With awbk.Sheets(i)
.UsedRange.Copy Range(strpasterange)
End With
Next

Set wbk = Nothing
Set awbk = Nothing
End Sub