Globally Named Range Problem

[Arturo]

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" & RowCnt).Address
End Sub

Appreciatively,
Arturo

 

[Bob Phillips]

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" &
RowCnt).Address
End Sub


--
HTH

Bob

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

 

[Arturo]

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

 

[Bob Phillips]

That is what you asked for.

--
HTH

Bob

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

 

[Arturo]

Bob,
I apologize if I was unclear; there are additional pieces that weren’t
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" & RowCnt_CLS).Address
End Sub

I do appreciate your input.
Best,
Arturo