Find Last Row and Name the range

[Tim]

Hello experts,
I'm having a problem doing something I thought was rather simple.
What I'm doing is copying a worksheet from another file which can have any
number of rows in it into the current workbook.
Once the worksheet is copied in, I want to name a range that will start on
A3:B3 and go to A?:B3.
So I need to determine the last row with data in it, and name that range
"Units".
The code I have below winds up naming the range above A3:B3... So A1:B3 is
what ends up being named "Units".
Please, any help would be great. Thanks.

Dim strLastCell As String
strLastCell = ActiveSheet.Range("A65536").End(xlUp).Row

ChDir "C:\TS_Reports"
Workbooks.Open Filename:="C:\TS_Reports\Export_CAT.xls"
Sheets("#CU# Units").Select
Sheets("#CU# Units").Copy Before:=Workbooks("ReportWriter.xlsx").Sheets(1)
Windows("Export_CAT.xls").Activate
ActiveWindow.Close
Windows("ReportWriter.xlsx").Activate

Sheets("#CU# Units").Select
Range("A3:B3").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveWorkbook.Names.Add Name:="Units", RefersToR1C1:= _
"='#CU# Units'!R3C1:R" & strLastCell & "C2"
Range("A3").Select

 

[joel]

Try these Changes. I used the address property of the range to get th
string to put into the Names referto property.



Dim strLastCell As String


Set bk = Workbooks.Open(Filename:="C:\TS_Reports\Export_CAT.xls")

bk.Sheets("#CU# Units").Copy _
Before:=ThisWorkbook.Sheets(1)
bk.Close savechanges:=False


With ThisWorkbook.Sheets("#CU# Units")
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
Set DataRange = .Range("A3:B" & LastRow)

ThisWorkbook.Names.Add Name:="Units", _
RefersToR1C1:="=" & DataRange.Address(external:=True
ReferenceStyle:=xlR1C1)
.Range("A3").Select
End Wit

 

[FSt1]

hi
your code has already select the range you want to name so why now do
something like this.
Range("A3:B3").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveWorkbook.Names.Add Name:="Units", RefersToR1C1:=Selection
Range("A3").Select

regards
FSt1

 

[Tim]

Thank you both.
FSt1 that's what I was looking to do, reference the selection I made somehow.