At times I need to change the source data used in some formulas.
When this happens I would like to remove the source data sheet, called
RawData, and import a new sheet that contains current data.
I am trying to write a macro that would define named dynamic ranges on
RawData.

Im using this code I copied from some other post:
ThisWorkbook.Names.Add Name:="dAge",
RefersTo:="=OFFSET(RawData!$H$1,0,0,COUNTA(RawData !$H:$H),1)", Visible:=True

This line doesnt leave any trace in the Name list; InsertNamePastePaste
List does not show €œdAge€�,
nor does a loop with €œDebug.Print crtName.Name, crtName.RefersTo,
crtName.Visible€�

Even worse, this simpler one doesnt work either
ThisWorkbook.Names.Add Name:="dAge7", RefersTo:="=RawData!$H$1:$H$10",
Visible:=True

When I add the name with InsertNameDefine this works:
=OFFSET(RawData!$H$1,0,0,COUNTA(RawData!$H:$H),1)

I would appreciate any help with this syntax or other suggestions for
updating/defining dynamic ranges in VB.

Thank you.

I named a sheet "RawData", put some values in the top of Col-H and tried
your code to add names. All worked fine as expected.

Your code adds names to ThisWorkbook, ie the wb with the code. When you are
looking in the names dialog is ThisWorkbook the activeworkbook. If not
that'll be the reason for the confusion.

Regards,
Peter T

"adimar" wrote in message
...
At times I need to change the source data used in some formulas.
When this happens I would like to remove the source data sheet, called
RawData, and import a new sheet that contains current data.
I am trying to write a macro that would define named dynamic ranges on
RawData.

I'm using this code I copied from some other post:
ThisWorkbook.Names.Add Name:="dAge",
RefersTo:="=OFFSET(RawData!$H$1,0,0,COUNTA(RawData !$H:$H),1)",
Visible:=True

This line doesn't leave any trace in the Name list;
InsertNamePastePaste
List does not show "dAge",
nor does a loop with "Debug.Print crtName.Name, crtName.RefersTo,
crtName.Visible"

Even worse, this simpler one doesn't work either
ThisWorkbook.Names.Add Name:="dAge7", RefersTo:="=RawData!$H$1:$H$10",
Visible:=True

When I add the name with InsertNameDefine this works:
=OFFSET(RawData!$H$1,0,0,COUNTA(RawData!$H:$H),1)

I would appreciate any help with this syntax or other suggestions for
updating/defining dynamic ranges in VB.

Thank you.

Ooops... Good point. I'm running all macros from a single add-in book and
overlooked the meaning of ThisWorkbook.

Thank you for your help.


"Peter T" wrote:

I named a sheet "RawData", put some values in the top of Col-H and tried
your code to add names. All worked fine as expected.

Your code adds names to ThisWorkbook, ie the wb with the code. When you are
looking in the names dialog is ThisWorkbook the activeworkbook. If not
that'll be the reason for the confusion.

Regards,
Peter T

"adimar" wrote in message
...
At times I need to change the source data used in some formulas.
When this happens I would like to remove the source data sheet, called
RawData, and import a new sheet that contains current data.
I am trying to write a macro that would define named dynamic ranges on
RawData.

I'm using this code I copied from some other post:
ThisWorkbook.Names.Add Name:="dAge",
RefersTo:="=OFFSET(RawData!$H$1,0,0,COUNTA(RawData !$H:$H),1)",
Visible:=True

This line doesn't leave any trace in the Name list;
InsertNamePastePaste
List does not show "dAge",
nor does a loop with "Debug.Print crtName.Name, crtName.RefersTo,
crtName.Visible"

Even worse, this simpler one doesn't work either
ThisWorkbook.Names.Add Name:="dAge7", RefersTo:="=RawData!$H$1:$H$10",
Visible:=True

When I add the name with InsertNameDefine this works:
=OFFSET(RawData!$H$1,0,0,COUNTA(RawData!$H:$H),1)

I would appreciate any help with this syntax or other suggestions for
updating/defining dynamic ranges in VB.

Thank you.