Copy list of workbooks ( one sheet only) to one workbook in tab number order

[David123456]

Can anyone assist with a formula.

Take a list of workbooks ( only1 worksheet in each workbook) say the workbooks are held in the y:\ and called 1980-001,1980-002 up to 1980-250

I want to copy all these individual workbooks to one workbook in tabs in file number order.

So the first tab would be 1980-001 the next tab would be 1980-002 and so on depending o how many workbooks there are.

My formula doe this but does not add the workbook name to the tab and also does not copy them over in number order.


Any help greatly appreciated

 

[Andrew Poulsom]

Silly me. How about?

Sub GetSheets()
    Dim Path As String
    Dim Filename As String
    Dim ShName As String
    Path = "Y:"
    Filename = Dir(Path & "*.xls")
    Do While Filename <> ""
        Workbooks.Open Filename:=Path & Filename, ReadOnly:=True
        ShName = Left(ActiveWorkbook.Name, InStr(ActiveWorkbook.Name, ".") - 1)
        ActiveWorkbook.Worksheets(1).Copy After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)
        ActiveSheet.Name = ShName
        Workbooks(Filename).Close
        Filename = Dir()
    Loop
End Sub

 

[David123456]

Andrew this works

But not putting the tabs in number order.


Thanks