Run time error in my macro
Posted by RoB on October 05, 2001 3:55 PM
I have this code which Robb came up with and it works fine, but when i make a modification, i get an error.
This is the working code:
Sub Directory_Search()
Dim myFile As String, myCurrFile As String
myCurrFile = ThisWorkbook.Name
myFile = Dir("S:\FileServer\Excel\Save FILES In Here\YEAR 2001\01-01\Cathy\*.xls")
Do Until myFile = ""
Workbooks.Open "S:\FileServer\Excel\Save FILES In Here\YEAR 2001\01-01\Cathy\" & myFile
Workbooks(myCurrFile).Worksheets("Sheet1").Range("A65536").End(xlUp).Offset(1, 0) = Workbooks(myFile).Worksheets("Application").Range("AI4")
Workbooks(myFile).Close savechanges:=False
myFile = Dir
Loop
End Sub
But it WILL NOT work like this:
Sub Directory_Search()
Dim myFile As String, myCurrFile As String
myCurrFile = ThisWorkbook.Name
myFile = Dir("S:\FileServer\Excel\Save FILES In Here\YEAR 2001\01-01\Cathy\*.xls")
Do Until myFile = ""
Workbooks.Open "S:\FileServer\Excel\Save FILES In Here\YEAR 2001\01-01\Cathy\" & myFile
Workbooks(myCurrFile).Worksheets("Sheet1").Range("A65536").End(xlUp).Activate
With ActiveCell
.Offset(1, 0) = Workbooks(myFile).Worksheets("Application").Range("AI4")
End With
Workbooks(myFile).Close savechanges:=False
myFile = Dir
Loop
End Sub
Notice I changed:
Workbooks(myCurrFile).Worksheets("Sheet1").Range("A65536").End(xlUp).Offset(1, 0) = Workbooks(myFile).Worksheets("Application").Range("AI4")
to:
Workbooks(myCurrFile).Worksheets("Sheet1").Range("A65536").End(xlUp).Activate
With ActiveCell
.Offset(1, 0) = Workbooks(myFile).Worksheets("Application").Range("AI4")
End With
I'm getting the error "Run time error '1004': Activate method of Range class failed. " with the bold part
I know its not much different, but for the sake of knowing, I'd like to know :)
Can someone help? Thanks :)