Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Can select range of sheet
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 553
Default Can select range of sheet
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
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 101
Default Can select range of sheet
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


Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 553
Default Can select range of sheet
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



Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 553
Default Can select range of sheet
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



Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 553
Default Can select range of sheet
Also note too that I am capable of selecting A2 so the line of code:

Worksheets("Template").Range("A2").Select

Does work. I can see it select the cell when I Step throught it. But it
fails to select the range after that.

Thanks



"ExcelMonkey" wrote:

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



Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5,302
Default Can select range of sheet
Hi ExcelMonkey,

Perhaps:

Rng.CurrentRegion.Select

---
Regards,
Norman



"ExcelMonkey" wrote in message
...
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





Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 553
Default Can select range of sheet
The problem here is that I do no want row 1. This contains Field Names that
I do not need. I was going to use .UsedRange originally.

Thanks

"Norman Jones" wrote:

Hi ExcelMonkey,

Perhaps:

Rng.CurrentRegion.Select

---
Regards,
Norman



"ExcelMonkey" wrote in message
...
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






Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5,302
Default Can select range of sheet
Hi ExcelMonkey,

I have already catered for this.

See my follow up post.


---
Regards,
Norman



"ExcelMonkey" wrote in message
...
The problem here is that I do no want row 1. This contains Field Names
that
I do not need. I was going to use .UsedRange originally.

Thanks

"Norman Jones" wrote:

Hi ExcelMonkey,

Perhaps:

Rng.CurrentRegion.Select

---
Regards,
Norman



"ExcelMonkey" wrote in message
...
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








Reply With QuoteReply With Quote
  #9   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5,302
Default Can select range of sheet
Hi ExcelMonkey,

Or:

Dim rng As Range

Set rng = Range("A2").CurrentRegion
Set rng = rng.Offset(1).Resize(rng.Rows.Count - 1)
rng.Select



---
Regards,
Norman



"Norman Jones" wrote in message
...
Hi ExcelMonkey,

Perhaps:

Rng.CurrentRegion.Select

---
Regards,
Norman



"ExcelMonkey" wrote in message
...
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







Reply With QuoteReply With Quote
  #10   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 553
Default Can select range of sheet
So this code worked for me:

Set tbl = Worksheets("Template").Range("A2").CurrentRegion
tbl.Offset(1, 0).Resize(tbl.Rows.Count - 1, tbl.Columns.Count).Select

However, I later want to copy the data in this range. I do the following:

tbl.Copy

However when i paste it. I get the entire range including Row 1. I was
using the resize method as I thought I would adjust the range I wanted to
copy. So although it resized it and selected this resized range, it copies
the original. How is this?????


Thanks




"Norman Jones" wrote:

Hi ExcelMonkey,

Or:

Dim rng As Range

Set rng = Range("A2").CurrentRegion
Set rng = rng.Offset(1).Resize(rng.Rows.Count - 1)
rng.Select



---
Regards,
Norman



"Norman Jones" wrote in message
...
Hi ExcelMonkey,

Perhaps:

Rng.CurrentRegion.Select

---
Regards,
Norman



"ExcelMonkey" wrote in message
...
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








Reply With QuoteReply With Quote
  #11   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 553
Default Can select range of sheet
This seems to work.

Set tbl = Worksheets("Template").Range("A2").CurrentRegion
tbl.Offset(1, 0).Resize(tbl.Rows.Count - 1, tbl.Columns.Count).Select

Thanks

"Norman Jones" wrote:

Hi ExcelMonkey,

Perhaps:

Rng.CurrentRegion.Select

---
Regards,
Norman



"ExcelMonkey" wrote in message
...
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






Reply With QuoteReply With Quote
  #12   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 553
Default Can select range of sheet
Or is the lesson of the day that I cannot use the select method on an object?
I took out the tbl.Select and simple went on to the next line tbl.Copy and
all is well.

Thanks

"ExcelMonkey" wrote:

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
Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Hybrid Mode Hybrid Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Can't Select Range off sheet from inputbox Jim May Excel Discussion (Misc queries) 4 September 29th 07 08:26 PM
Select data to appear on 2nd sheet by date range... Cbreze Excel Discussion (Misc queries) 0 June 28th 07 12:10 AM
select a range, copy it to a new sheet Dave F Excel Discussion (Misc queries) 1 September 22nd 06 08:06 PM
Select Sheet then Select Range Gee[_2_] Excel Programming 3 May 27th 04 10:10 PM
Automatically select range and copy to new sheet Alistair[_2_] Excel Programming 3 October 11th 03 04:33 AM


All times are GMT +1. The time now is 11:23 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement