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

 

[David123456]

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


Does not work Comes back with run time error.

 

[David123456]

ActiveSheet.Name = Left(ActiveWorkbook.Name, InStr(ActiveWorkbook.Name, ".") - 1)

Andrew,

This formula it comes back with a run time error.

 

[Andrew Poulsom]

On which line and at what stage? What is the text of the error?

 

[David123456]

Hi Andrew

Run time error "5": invalid procedure call or argument

 

[Andrew Poulsom]

On which line? I'm not psychic.

 

[David123456]

ActiveSheet.Name = Left(ActiveWorkbook.Name, InStr(ActiveWorkbook.Name, ".") - 1)

 

[Andrew Poulsom]

That line of code works for me. Doesn't the workbook you opened have an extension?

 

[David123456]

Just .xls if that's what you are referring too.

 

[Andrew Poulsom]

When you get the error go to the Immediate window (press Ctrl+G if you can't see it), type:

?ActiveWorkbook.Name

and press Enter. What does it return (in the row below).

 

[David123456]

Book1