MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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 :)


Posted by Henry Root on October 05, 2001 8:20 PM

Try :-
Workbooks(myCurrFile).Worksheets("Sheet1").Range("A65536").End(xlUp).Select

The original code is more efficient than your revised code. Why do you want to change it?


Posted by RoB on October 06, 2001 12:30 AM

I have tried that...any other ideas?

I have tried using:
Workbooks(myCurrFile).Worksheets("Sheet1").Range("A65536").End(xlUp).Select
but i still get the same error. I'm trying to use this code because there are more cells im going to be copying, so instead of typing the whole line, i would like to just use the "selected cell.offset = whatever". Thanks for the response though. Any other ideas?

Posted by Henry Root on October 06, 2001 4:43 AM

Re: I have tried that...any other ideas?

Sorry, was dreaming with my previous response.
You can't use Select or Activate when specifying cell refs on a sheet that is not the active sheet. First you have to select the sheet before you can select cells on the sheet.
You can, however, use the Value or Formula properties to get the value or formula in a cell on another sheet without selecting the sheet and can also use Copy and Paste without selecting the sheet.