Reading differrent excel sheets

hi, thanks for your reply, yes i know that I have to use macro, but I don't know how to do that. or how to start. wel Alt+F11, but from there I have no clue. Is there any steps or guidances that I can follow?

You need to fully and completely explain your problem. Saying that "consolidate the data from different excel sheets (+/- 20/ files) from differrent excel files (20) how can I do it quickly in excel by importing the excels sheets and consolidate the total sheets. " does not help any one. Again fully and completely explain your issue. Ideally upload some sample books (with some sample data) to show how the books look like and how would you like to see. The data does not need to be real but a good represtation of how it is. If you upload your books at some shared site and post back link back here and fully explain the issue in DETAIL, some one might be able to help

Hi, I see.
http://www.megaupload.com/?d=9RYRDUIQ
I have upload the files. for example there are file A and file B and 1 consolidate file which contains 1 (or more) sheet from file A and 1 (or more) sheet from file B, and a total sheet would be the sum of the all sheets (same format) in the consolidate file.

of course I could do it manually with copy/paste but there are more files and a lot of different sheets which i have to use and group in different way. also the procedure is repeated so I would like to learn though macro how can I do it efficiently.

thanks a lot in advance for your help

Ah so all you are looking for is simply copy all the sheets from all workbooks in a particular dir into one workbook. You would run into one issue. It seems that some of your sheets have formulas. I am not sure if they will not fail when you move/copy the sheets (in case they were refering to some other sheet of same workbook or some other sheet on some other workbook). In case that is not so, you can use some thing like this

Sub doCopyAllWorkbooks()
   Dim wbOpen              As Workbook
   Dim wbNew               As Workbook
   Dim sFile               As String
   Dim ws                  As Worksheet
   Dim sSheetName          As String
   Dim sFileName           As String
   Dim sPath               As String
 
'change this line to point to right location  
   sPath = "C:\Users\haadi\Downloads\excel help\"
   
   Application.ScreenUpdating = False
   Application.Calculation = xlCalculationManual
   
   ChDir sPath
  sFile = Dir(sPath & "*.xls*")
       
   Set wbNew = Workbooks.Add
   wbNew.SaveAs Filename:=sPath & "Consolidated_" & Format(Now, "_YYYYMMDD_HHNNSS"), FileFormat:=xlWorkbookNormal
       
   Do While sFile <> vbNullString
       Set wbOpen = Workbooks.Open(sPath & sFile)
       With wbOpen
         sFileName = wbOpen.Name
         sFileName = Left(sFileName, InStrRev(sFileName, ".") - 1)
         For Each ws In .Worksheets
            sSheetName = ws.Name
            Select Case sSheetName
               Case Is = "Instructions"
               Case Else
                  ws.Copy After:=wbNew.Sheets(wbNew.Sheets.Count)
                  wbNew.Sheets(wbNew.Sheets.Count).Name = sSheetName & " " & sFileName
            End Select
         Next
         .Close SaveChanges:=False
       End With
       sFile = Dir
   Loop
           
   Application.ScreenUpdating = True
   Application.Calculation = xlCalculationAutomatic

End Sub

hi, thanks a lot, I'll try this one