Hello,
Im wondering if theres 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
its 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

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

VLU is added to sheet("abc") but is not accessable globally

"Bob Phillips" wrote:

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

That is what you asked for.

--
HTH

Bob

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

"Arturo" wrote in message
...
VLU is added to sheet("abc") but is not accessable globally

"Bob Phillips" wrote:

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

Bob,
I apologize if I was unclear; there are additional pieces that werent
mentioned. Another sub accesses that global VLU populating another scalable
range in different sheet. What is resolving the other issues is as follows:

Sub ScaleVLU()
Dim RowCnt_CLS As Integer

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

I do appreciate your input.
Best,
Arturo


"Bob Phillips" wrote:

That is what you asked for.

--
HTH

Bob

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

"Arturo" wrote in message
...
VLU is added to sheet("abc") but is not accessable globally

"Bob Phillips" wrote:

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