Subscript Error

[teresa]

Hi, I get 'Subscript Out Of Range' for the code below, even though
"Natwest1.xls"
does exist in that format, thanks


Sub coi()

Set fin = Application.Workbooks("C:\My Documents\Natwest1.xls")
lastrow = Cells(Rows.Count, 3).End(xlUp).Row
For i = 3 To lastrow
If Cells(i, 4) = "Hudson" Then
Cells(i, 4).EntireRow.Copy Destination:=fin.Cells(91, 1)
End If
Next


End Sub

[Dave Peterson]

Try:
Set fin = Application.Workbooks("Natwest1.xls")
You don't need and can't have the path in that statement.

But it looks like you're gonna have some trouble with this:

Cells(i, 4).EntireRow.Copy Destination:=fin.Cells(91, 1)
maybe...
Cells(i, 4).EntireRow.Copy Destination:=fin.worksheets("sheet1").Cells(91, 1)



teresa wrote:
>
> Hi, I get 'Subscript Out Of Range' for the code below, even though
> "Natwest1.xls"
> does exist in that format, thanks
>
> Sub coi()
>
> Set fin = Application.Workbooks("C:\My Documents\Natwest1.xls")
> lastrow = Cells(Rows.Count, 3).End(xlUp).Row
> For i = 3 To lastrow
> If Cells(i, 4) = "Hudson" Then
> Cells(i, 4).EntireRow.Copy Destination:=fin.Cells(91, 1)
> End If
> Next
>
> End Sub

--

Dave Peterson

[Tom Ogilvy]

If it is open then it should be

set fin = workbooks("Natwest1.xls")

if you want to open it, then it should be

Set fin = Application.Workbooks.Open("C:\My Documents\Natwest1.xls")

Your half way between, so I can't tell what you want to do.

--
Regards,
Tom Ogilvy

"teresa" <[email protected]> wrote in message
news:[email protected]...
> Hi, I get 'Subscript Out Of Range' for the code below, even though
> "Natwest1.xls"
> does exist in that format, thanks
>
>
> Sub coi()
>
> Set fin = Application.Workbooks("C:\My Documents\Natwest1.xls")
> lastrow = Cells(Rows.Count, 3).End(xlUp).Row
> For i = 3 To lastrow
> If Cells(i, 4) = "Hudson" Then
> Cells(i, 4).EntireRow.Copy Destination:=fin.Cells(91, 1)
> End If
> Next
>
>
> End Sub
>