Interestingly enough, the code below works on its own if manuall click onto
the spreadsheet and the sheet "template":

Set rng = Worksheets("Template").Range("A2")
Range(rng, rng.End(xlDown).End(xlToRight)).Select

There must be something going on with what I am doing prior to that. Does
it hav to do with the activation of spreadsheets. I am going back and forth
between two workbooks. See the previous post.

Thanks





"ExcelMonkey" wrote:

That doesn't work either. Note I have replaced my lines of code with yours
below. Interestingly enough when I go to the immediate window I get:

?rng.End(xlToright).Address
$G$2
?rng.End(xlDown).Address
$A$24

This seems to make sense. It seems to be failing on .Select part of the
statment.



Private Sub CommandButton1_Click()
Dim FName As Variant
Dim ExtractedFileName As Variant
Dim ThisFileName As Variant
Dim FirstEmptyRow As Double
Dim rng As Range

ThisFileName = ThisWorkbook.Name

FName = Application.GetOpenFilename("AÂ*ll files (*.*),*.*")
If FName = False Then
' user clicked cancel
Else

Workbooks.Open Filename:=FName

'Extract file Name from full path
ExtractedFileName = sFileName(FName)
Windows(ExtractedFileName).Activate

'Select First Cell in Target file sheet
Worksheets("Template").Range("A2").Select

'Choose Used Range of target sheet
Set rng = Worksheets("Template").Range("A2")
Range(rng, rng.End(xlDown).End(xlToRight)).Select

'Range(Selection, Selection.End(xlDown)).Select
'Range(Selection, Selection.End(xlToRight)).Select

End If
End Sub

Function sFileName(sFullname As Variant) As String
If InStrRev(sFullname, "\") = 0 Then
sFileName = sFullname
Else
sFileName = Mid$(sFullname, _
InStrRev(sFullname, "\") + 1)
End If
End Function



"Mangesh Yadav" wrote:

Your code works for me.

You could also use:

Set rng = Worksheets("Sheet1").Range("A2")
Range(rng, rng.End(xlDown).End(xlToRight)).Select


Mangesh



"ExcelMonkey" wrote in message
...
I am trying to select cell A2 and the SHIFT-CNTL-Right and
SHIFT-CNTL-Down.
When I recored the macro the code is as seen below under the comment
'Choose Used Range of target sheet. However when I try to run it, I get
an
Run Time Error 1004. ("Method Range of Object _Worksheet failed")


Why is this?

'Select First Cell in Target file sheet
Worksheets("Template").Range("A2").Select

'Choose Used Range of target sheet
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select

Thanks