copy and paste it in a new sheet with sheet name as a1 value

[vicky]

I have many work books in one folder . want to copy data from every
workbook to
the resultworkbook and the sheet names should be of a1 value .

 

[vicky]

thanks a lot joel...

 

[vicky]

i have tried this code as wel . its working .... thamks joel for
taking time to answer my questions....

Set NewWkb = ThisWorkbook
MyPath = "C:\Documents and Settings\vb\"
ChDir MyPath
TheFile = Dir("*.xls")
Do While TheFile <> ""
Set wb = Workbooks.Open(MyPath & "\" & TheFile)
Set obj = wb.Sheets(1)
obj.Cells.Copy
NewWkb.Sheets.Add before:=NewWkb.Sheets(NewWkb.Sheets.Count)
sht.Copy after:=.Sheets(.Sheets.Count)
With NewWkb.ActiveSheet
..Range("a1").PasteSpecial xlPasteValues
..Name = obj.Range("a1").Value
End With
obj.Copy after:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
ActiveSheet.Name = obj.Range("A1")
wb.Close
TheFile = Dir
Application.CutCopyMode = False

Loop