Copy Mutiple worksheets to single Workbook

[ExcelJA]

Hi-ho all.

I have several Excel 2003 (SP3) workbooks that contain multiple worksheets and I would like to move the data to ONE single workbook. The data is held within a fixed range in all the worksheets. Its easy to cut and paste special with only the values, but its tedious and with over 6000 records its tiring!

So am looking for some guidance so that I can either create a macro that will find open the individual workbooks and create a single worksheet or some other approach that saves me some time.

Looking forward to your replies.

 

[ExcelJA]

Found the solution!!
Sub consolidate()
On Error Resume Next
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Sheets("ConsolidatedData").Delete
Sheets.Add before:=Sheets(1)
Sheets(1).Name = "ConsolidatedData"
Dim ii As Long
For ii = 3 To 4
Sheets(ii).Range("A1:AA1000").Copy 'change the range to copy
With Sheets(1).Cells(Rows.Count, 1).End(xlUp).Offset(1)
.PasteSpecial xlValues
End With
Next ii
Application.DisplayAlerts = True
Application.ScreenUpdating = True
Range("a1").Activate
End Sub