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