Problem with inserting Name Range in macro

[Guest]

Afternoon,

I have this macro:

Sub NameSheets()
'will add a sheet, and name it
'for each name in column A
'from A1 down till it hits a blank row
Dim Rng As Range
Dim ListRng As Range
Set ListRng = Range(Range("A1"), Range("A1").End(xlDown))
For Each Rng In ListRng
If Rng.Text <> "" Then
With Worksheets
..Add(after:=.Item(.Count)).Name = Rng.Text
End With
End If
Next Rng
End Sub

Which creates the number of sheets according to the number of names I have
listed.

Now I have a formula above the list of names:

=IF($B$3<>"",ADDRESS(ROW($B$3),COLUMN($B$3),1)&":"&ADDRESS(ROW($B$3)+COUNTA($B$3:$B$104)-1,COLUMN($B$3)),"")

The current formula result is B4:B100, but this will change month to month
depending on how many names are listed. I have named the formula answer as
Name Range.
With the created name range substituted in
place of A1 in the macro, it comes back with an error about having entered an
invalid name try name not exceeding 31 characters or make sure name does not
contain : \ / ? * [ or ]

So I ask if someone can assist in inserting a name range instead of A1 as
the number of names will change month to month.

Thanks

 

[Steve Schapel]

Sunny,

Your question appears to be about Excel. You will have a better chance
of a good answer in an Excel-related newsgroup.

 

[Guest]

I put the same question in the Excel group. Still waiting.

Thanks, have a good weekend.

Sunny,

Your question appears to be about Excel. You will have a better chance
of a good answer in an Excel-related newsgroup.

--
Steve Schapel, Microsoft Access MVP

Afternoon,

I have this macro:

Sub NameSheets()
'will add a sheet, and name it
'for each name in column A
'from A1 down till it hits a blank row
Dim Rng As Range
Dim ListRng As Range
Set ListRng = Range(Range("A1"), Range("A1").End(xlDown))
For Each Rng In ListRng
If Rng.Text <> "" Then
With Worksheets
.Add(after:=.Item(.Count)).Name = Rng.Text
End With
End If
Next Rng
End Sub

Which creates the number of sheets according to the number of names I have
listed.

Now I have a formula above the list of names:

=IF($B$3<>"",ADDRESS(ROW($B$3),COLUMN($B$3),1)&":"&ADDRESS(ROW($B$3)+COUNTA($B$3:$B$104)-1,COLUMN($B$3)),"")

The current formula result is B4:B100, but this will change month to month
depending on how many names are listed. I have named the formula answer as
Name Range.
With the created name range substituted in
place of A1 in the macro, it comes back with an error about having entered an
invalid name try name not exceeding 31 characters or make sure name does not
contain : \ / ? * [ or ]

So I ask if someone can assist in inserting a name range instead of A1 as
the number of names will change month to month.

Thanks