Sub wjhb()Dim str As StringDim wb As Workbookstr = Dir("d:\data\*.xls*")For i = 1 To 100 Set wb = Workbooks.Open("d:\data\" & str) wb.Sheets(1).Copy after:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count) ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count).Name = Split(wb.Name, ".")(0) wb.Close str = Dir If str = "" Then Exit For End IfNextEnd Sub
Sub wjhb()Dim str As StringDim wb As WorkbookDim sht As Worksheetstr = Dir("d:\data\*.xls*")For i = 1 To 100 Set wb = Workbooks.Open("d:\data\" & str) For Each sht In wb.Sheets sht.Copy after:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count) ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count).Name = Split(wb.Name, ".")(0) & sht.Name Next wb.Close str = Dir If str = "" Then Exit For End IfNextEnd Sub