A
adimar
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; Insert>Name>Paste>Paste
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 Insert>Name>Define 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.
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; Insert>Name>Paste>Paste
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 Insert>Name>Define 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.