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

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

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

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

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

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

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

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

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

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

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

Hi ExcelMonkey,

The line:

tbl.Offset(1, 0).Resize(tbl.Rows.Count - 1,
tbl.Columns.Count).Select

merely selects a subset of the tbl range; it does not change tbl.

Consequently, when you later select tbl, you select the original tbl range,
which has not changed.

Conversely, my in my code:

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

the rangg which corresponds to rng is changed by my second line assignment,
and, consequently, the altered range is selected in line 3.

I used the rng variable. in accordance with your original code. By all means
change rng in my code to tbl.

---
Regards,
Norman



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

Hi ExcelMonkey,

Consequently, when you later select tbl, you select the original tbl
range, which has not changed.

should have read:

'========================
Consequently, when you later copy tbl, you copy the original tbl range,
which has not changed
'========================

I inadvertently used 'select' instead of 'copy' and, although this does not
in any way change the basic proposition, it may well serve to confuse.


---
Regards,
Norman



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

The line:

tbl.Offset(1, 0).Resize(tbl.Rows.Count - 1,
tbl.Columns.Count).Select

merely selects a subset of the tbl range; it does not change tbl.

Consequently, when you later select tbl, you select the original tbl
range, which has not changed.

Conversely, my in my code:

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

the rangg which corresponds to rng is changed by my second line
assignment, and, consequently, the altered range is selected in line 3.

I used the rng variable. in accordance with your original code. By all
means change rng in my code to tbl.

---
Regards,
Norman



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

So I am having one hell of a bad day here in VBA. I think I am going to put
my fist throug my screen. WHY cannot I select this range tbl????? The code
gets down the line tbl.Select and then I get a Run Time Error 1004 Select
method of Range Class failed.

I have used your code instead, and replaced the variable rng with tbl. Have
I dimensioned the variable properly. Its dim as Object. I have not been
able to Select anyting with this code. Driving me crazy.


Private Sub CommandButton1_Click()
Dim FName As Variant
Dim ExtractedFileName As Variant
Dim ThisFileName As Variant
Dim FirstEmptyRow As Double
Dim tbl As Object
Dim Revisedtble As Object
Dim ImportSheet As Worksheet

ThisFileName = ThisWorkbook.Name
'ImportSheet = ThisWorkbook.Sheets(2).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
Worksheets("Template").Select

'Select First Cell in Target file sheet
Set tbl = Range("A2").CurrentRegion
Set tbl = tbl.Offset(1).Resize(tbl.Rows.Count - 1)
tbl.Select


'Copy Range
tbl.Copy

Windows(ThisFileName).Activate
Worksheets("Import Data").Select

'Find and Select Paste Cell of This file
FirstEmptyRow =
Application.WorksheetFunction.CountA(Worksheets("I mport Data").Range("A:A"))
Worksheets("Import Data").Range("A1").Offset(FirstEmptyRow, 0).Select
ActiveSheet.Paste

End If
End Sub



"Norman Jones" wrote:

Hi ExcelMonkey,

Consequently, when you later select tbl, you select the original tbl
range, which has not changed.

should have read:

'========================
Consequently, when you later copy tbl, you copy the original tbl range,
which has not changed
'========================

I inadvertently used 'select' instead of 'copy' and, although this does not
in any way change the basic proposition, it may well serve to confuse.


---
Regards,
Norman



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

The line:

tbl.Offset(1, 0).Resize(tbl.Rows.Count - 1,
tbl.Columns.Count).Select

merely selects a subset of the tbl range; it does not change tbl.

Consequently, when you later select tbl, you select the original tbl
range, which has not changed.

Conversely, my in my code:

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

the rangg which corresponds to rng is changed by my second line
assignment, and, consequently, the altered range is selected in line 3.

I used the rng variable. in accordance with your original code. By all
means change rng in my code to tbl.

---
Regards,
Norman



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

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