Naming a range with VBA - Scope Workbook

[braindiesel]

As the subject says, I want to name a range with a workbook scope.

Here is a sample of the code

Selection.Resize(25, 12).Select
actualrange = Selection.Address
ActiveWorkbook.Names.Add Name:="actualSales1", RefersTo:=Sheets("Simply Actual").Range(actualrange)

This is setting the range name to the sheet. How can I change the scope with VBA?

Your help is greatly appreciated,

Glen

 

[PJ_in_FL]

To set a name with WORKBOOK scope, use:

ActiveWorkbook.Names.Add

To set a name with WORKSHEET scope, use:

ActiveWorkbook.Worksheets("Sheet1").Names.Add

Set "Sheet1" to be the name of the applicable worksheet.

 

[braindiesel]

Hi PJ

My original code was simply this
ActiveWorkbook.Names.Add Name:="actualSales1", RefersTo:=selection

that is creating a name in the sheet, not the workbook.

The code I posted above was an effort to make the name global. still no avail.

Any suggestions?

 

[RoryA]

That code should create a workbook scope name, not a sheet one.
You could also use:

selection.name = "actualSales1"

 

[mikerickson]

I can't reproduce the error.

Perhaps there is a misunderstanding about the word "scope".

The OP code creates (on my machine) a name "actualSales1". It is scoped at the workbook level, which means that a formula (like =SUM(actualSales1)) will return the sum of all of those cells, no matter what sheet holds the formula.
The formula =SUM(actualSales1) when placed in Sheet1!C1 will sum the same cells as the same formula when placed in Sheet2!C1. The name "actualSales1" has meaning on all sheets. Its "scope" is at the workbook level.

PJ_in_FL's code that specifies a particular worksheet, results in a name that is scoped to one sheet. i.e. the formula =SUM(actualSales1) will result in a #NAME error if put on any sheet than Sheet1. The name actualSales1 has meaning on only one worksheet. It is scoped at the worksheet level.

If you are looking for a named range where the sheet it refers to is relative would be defined like
Name: myName
RefesTo: !$A$1:$A$10

The formula =SUM(myName) would return the sum of cells A1:A10 of the same sheet that holds the formula. i.e. the formula =SUM(myName) placed in Sheet1!C1 would sum different cells than the formula =SUM(myName) placed in Sheet2!C1. This is not "scope", but relative sheet addressing.


The VB code for a name that is scoped at the workbook level with relative sheet addressing would be

ActiveWorkbook.Names.Add Name:="myName", RefersToR1C1:="=!" & Selection.Address(True, True, xlR1C1)

 

[braindiesel]

Thanks everyone for the replies

Rorya, this one
selection.name = "actualSales1"

solved it.

Thank you