Select method of range class failed

[sa02000]

I keep getting
Run time error '1004'
Select method of Range class failed from this. I don't see anything wrong with it. Any help??




Sub Import()

Dim strname As String

strname = ActiveWorkbook.Name
Workbooks.Open "C:\Macro_Practice\Sep_download.xls"
' wkbBook = Workbooks("Sep_download.xls")

Workbooks("Sep_download.xls").Worksheets(1).Copy before:=Workbooks(strname).Worksheets(3)
Workbooks("Sep_download.xls").Close savechanges:=True

Sheets("sheet2").Select
ActiveWindow.SelectedSheets.Delete

Sheets("sep_download").Select
Range("I2").Select
ActiveCell.FormulaR1C1 = "=Trim(RC[-8])"
Range("I2").Select
Call GetRealLastCell
Selection.AutoFill Destination:=Range(I2, RealLastRow)

End Sub

Sub GetRealLastCell()
Dim RealLastRow As Long
Dim RealLastColumn As Long
Range("A1").Select
On Error Resume Next
RealLastRow = _
Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row
RealLastColumn = _
Cells.Find("*", [A1], , , xlByColumns, xlPrevious).Column
Cells(RealLastRow, RealLastColumn).Select
End Sub

[K Dales]

I see a problem. In the last line of Sub Import() you have:
Selection.AutoFill Destination:=Range(I2, RealLastRow)
But, first of all, I2 would be interpreted here as a variable name, not a
cell address as I think you want it to be; and also RealLastRow has no
meaning within the Sub Import() - it is defined only within the Sub
GetRealLastCell(). I don't know if this is what is giving you the error
message, since the message is specific to Range.Select. It would help to
know what line the code stops on when it errors out.

To fix: it appears to me that at the time you get to the line I mentioned
the range you want autofilled will be selected, so I think you can replace
that line with this instead:
Range("I2").AutoFill Destination:=Selection
--
- K Dales


"sa02000" wrote:

>
> I keep getting
> Run time error '1004'
> Select method of Range class failed from this. I don't see anything
> wrong with it. Any help??
>
>
>
>
> Sub Import()
>
> Dim strname As String
>
> strname = ActiveWorkbook.Name
> Workbooks.Open "C:\Macro_Practice\Sep_download.xls"
> ' wkbBook = Workbooks("Sep_download.xls")
>
> Workbooks("Sep_download.xls").Worksheets(1).Copy
> before:=Workbooks(strname).Worksheets(3)
> Workbooks("Sep_download.xls").Close savechanges:=True
>
> Sheets("sheet2").Select
> ActiveWindow.SelectedSheets.Delete
>
> Sheets("sep_download").Select
> Range("I2").Select
> ActiveCell.FormulaR1C1 = "=Trim(RC[-8])"
> Range("I2").Select
> Call GetRealLastCell
> Selection.AutoFill Destination:=Range(I2, RealLastRow)
>
> End Sub
>
> Sub GetRealLastCell()
> Dim RealLastRow As Long
> Dim RealLastColumn As Long
> Range("A1").Select
> On Error Resume Next
> RealLastRow = _
> Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row
> RealLastColumn = _
> Cells.Find("*", [A1], , , xlByColumns, xlPrevious).Column
> Cells(RealLastRow, RealLastColumn).Select
> End Sub
>
>
> --
> sa02000
> ------------------------------------------------------------------------
> sa02000's Profile: http://www.excelforum.com/member.php...o&userid=27747
> View this thread: http://www.excelforum.com/showthread...hreadid=473117
>
>