Add a msgbox displaying the workbook name to make surre you are deleting the
corect name. Also make sure you have the .XLS as part of the book name.

"Sandy" wrote:

I am getting the following error at the asterisked line:-

"Run-time error'9': Subscript out of range".

Can someone point out why?

Sub TransferData()

Dim NewBookPath As String
Dim OldBookPath As String
Dim OldBook As String
Dim OldBookName As Workbook
Dim NewBook As String
Dim Lr As Long
Dim ThisBookName As String


ThisBookName = ThisWorkbook.Name
OldBook = Left(ThisBookName, 22) & Mid(ThisBookName, 26,
Len(ThisBookName) - 22)

NewBookPath = ThisWorkbook.Path
OldBookPath = NewBookPath & "\" & OldBook

NewBook = ActiveWorkbook.Name

Application.EnableEvents = False
Application.ScreenUpdating = False

*******
Set OldBookName = Workbooks(OldBook)
********

#####More code######

End Sub

Thanks
Sandy