Public MASTERwb As Workbook

Sub ScaleVLU()
Dim RowCnt As Integer

Set MASTERwb = ActiveWorkbook
MASTERwb.Sheets("abc").Select
RowCnt = MASTERwb.Sheets("abc").UsedRange.Rows.Count
MASTERwb.Names.Add Name:="abc!VLU", _
RefersTo:="=" & MASTERwb.Sheets("abc").Range("A1:D" &
RowCnt).Address
End Sub


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Arturo" wrote in message
...
Hello,
I'm wondering if there's away to add a named range to a specified sheet
without selecting that sheet first. I can get a row count from a sheet
while
it's not active.

Public MASTERwb As Workbook

Sub ScaleVLU()
Dim RowCnt As Integer

Set MASTERwb = ActiveWorkbook
MASTERwb.Sheets("abc").Select
RowCnt = MASTERwb.Sheets("abc").UsedRange.Rows.Count
MASTERwb.Names.Add Name:="VLU", _
RefersTo:="=" & MASTERwb.Sheets("abc").Range("A1:D" &
RowCnt).Address
End Sub

Appreciatively,
Arturo