Creating a Dynamic Named Range Using Sheet Name and Column Header

[burl_rfc_h]

Is their a simplified method of creating a dynamic range using the
column header and the sheet name.

For example suppose that in cell A2 I have a column header such as Date
Machined, then beneath A2 I have a number of cells containing dates,
obviously creating a dynamic range using the Insert Name Define from
cell A2 would be as written below

=OFFSET(Sheet1!$A$2,1,0,COUNTA(Sheet1!$A:$A)-1,1), the range name in
this case would be Date_Machined

I have a number of columns with column headers that I'd like to apply a
dynamic range but also using the sheet name, let's say the above
example would be Date_Machined_Sheet1

To simply things if I select the appropriate column then run the macro
it would suffice nicely.

 

[Bob Phillips]

Dim sName As String

sName = Cells(1, ActiveCell.Column).Value
ThisWorkbook.Names.Add Name:=sName, _
RefersTo:="OFFSET(" & sName & "_Sheet1!" & Cells(2,
ActiveCell.Column).Address & _
",1,0,COUNTA(" & sName & "_Sheet1!" & _
ActiveCell.EntireColumn.Address & ")-1,1)"


--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

 

[burl_rfc_h]

Bob,

I tried your solution, I think it's really close with a few tweaks
(sorry)....

Firstly, ThisWorkbook.Names.Add Name:=sName, _
how can I get it to include the sheet name for example suppose A2 has
Date Machined as the column header and the sheet name is Sheet1
then the range name would be Date_Machined_Sheet1
as a suggestion, do you think it would be best to set the worksheet
name to a variable, then call this out with sName.

Secondly, when I run the macro as written it includes the quotation
marks around the formula
="OFFSET(Sheet1!$A$2,1,0,COUNTA(Sheet1!$A:$A)-1,1)"
only after removing the quoation marks does the dynmanic range work.

 

[Tom Ogilvy]

Dim sName As String

sName = Cells(1, ActiveCell.Column).Value
ThisWorkbook.Names.Add Name:=sName & "_Sheet1", _
RefersTo:="=OFFSET(" & sName & "1!" & Cells(2, _
ActiveCell.Column).Address & _
",1,0,COUNTA(" & sName & "!" & _
ActiveCell.EntireColumn.Address & ")-1,1)"

 

[burl_rfc_h]

tom,

The formula is now correct, thank you. However, the range name is still
not right. In the following code you can see that my sheet name is
"MSG", in cell A2 I have "test_here", for the actual range name the
macro named it "_MSG, it should have been "test_here_MSG", what do you
think is wrong?

Dim sName As String

sName = Cells(1, ActiveCell.Column).Value
ThisWorkbook.Names.Add Name:=sName & "_MSG", _
RefersTo:="=OFFSET(" & sName & "MSG!" & Cells(2, _
ActiveCell.Column).Address & _
",1,0,COUNTA(" & sName & "MSG!" & _
ActiveCell.EntireColumn.Address & ")-1,1)"

Regards
Burl

 

[burl_rfc_h]

Tom,

I substituted the actual sheet name in the following code, the formula
is now correct, but the range name only came up with "_MSG", in cell A2
I had the following text "test here", so therefore the range name
should have been "test_here_MSG", (I used to quotation marks in the
preceeding example just to hightlight the area of concern)

Dim sName As String

sName = Cells(1, ActiveCell.Column).Value
ThisWorkbook.Names.Add Name:=sName & "_MSG", _
RefersTo:="=OFFSET(" & sName & "MSG!" & Cells(2, _
ActiveCell.Column).Address & _
",1,0,COUNTA(" & sName & "MSG!" & _
ActiveCell.EntireColumn.Address & ")-1,1)"

Thanks
Burl

 

[Tom Ogilvy]

If you want it dynamic based on the name of the active sheet

If I run this

Sub abc()
Dim s1 As String
Dim s2 As String
Dim sName As String
s1 = ActiveSheet.Name
s2 = "'" & s1 & "'"
sName = Cells(2, 1).Value
ThisWorkbook.Names.Add Name:=sName & "_" & s1, _
RefersTo:="=OFFSET(" & s2 & "!" & Cells(2, _
ActiveCell.Column).Address & _
",1,0,COUNTA(" & s2 & "!" & _
ActiveCell.EntireColumn.Address & ")-1,1)"

Debug.Print sName & "_" & s1, ThisWorkbook.Names( _
sName & "_" & s1).RefersTo

End Sub

I get
Test_Here_MSG =OFFSET(MSG!$E$2,1,0,COUNTA(MSG!$E:$E)-1,1)

A2 contains "Test_Here (you can't have a space in the name)

The activecell was in Column E


In your original request, I believe you said you wanted to the beginning of
the name from the cell in the first row of the column containing the
activecell. This modification looks specifically at A2 as you now state.

 

[burl_rfc]

Tom,

I ended up changing the sName callout from
sName = Cells(2, 1).Value
to
sName = Cells(2, ActiveCell.Column).Value

Then everything worked fine so long as no spaces existed in the column
header, lastly I modified the dynamic formula a little, see code below.

Many thanks for hepling me out on this one, it will save a great deal
of time.

Regards
Burl

Dim s1 As String
Dim s2 As String
Dim sName As String
s1 = ActiveSheet.Name
s2 = "'" & s1 & "'"

sName = Cells(2, ActiveCell.Column).Value
ThisWorkbook.Names.Add Name:=sName & "_" & s1, _
RefersTo:="=OFFSET(" & s2 & "!" & Cells(2, _
ActiveCell.Column).Address & _
",1,0,COUNT(" & s2 & "!" & _
ActiveCell.EntireColumn.Address & "),1)"


Debug.Print sName & "_" & s1, ThisWorkbook.Names( _
sName & "_" & s1).RefersTo

 

[gimme_this_gimme_that]

Just thought I'd mention what everyone probably already knows - but it
hasn't been said.

Once you create a range you can extend it by adding a new interior row
or column.