Name a range in dynamically created excel sheet using VB.NET

Question

I want to name a range in an excel sheet that is created dynamically. I tried following but it is not working.

With oWB
  For intCount = 1 To wbnum1
    .Worksheets.Add(After:=.Worksheets(intCount + 1)) 
    .Sheets(intCount + 1).Name = "Sheet" & intCount.ToString()
    .Sheets(intCount + 1).Cells(19, 2).value = "Sheet" & intCount.ToString
    .Sheets(intCount+1).Range("A1:Z1")
    .Range.Name = "Numbers"
  Next

what is wrong with this?

Thanks

Try: .Sheets(intCount+1).Range("A1:Z1").Name = "Numbers"

Tim Williams
– Tim Williams

2012-11-01 04:48:28 +00:00
Commented Nov 1, 2012 at 4:48 — Tim Williams
– Tim Williams, Commented Nov 1, 2012 at 4:48

Daniel · Accepted Answer · 2012-11-01 05:01:16Z

2

You aren't specifying a Range.

Presumably, you should merge your last and second to last line:

.Sheets(intCount+1).Range("A1:Z1").Name = "Numbers"

However, from experimentation it appears that assigning the name this way creates workbook level names resulting in only the last assignment being valid after you loop through your worksheets.

An alternate/safer way to do this is to add the name as a worksheet specific name via the worksheet's names collection:

.Sheets(intCount+1).Names.Add "Numbers", .Sheets(intCount+1).Range("A1:Z1")

answered Nov 1, 2012 at 5:01

Daniel

13.1k3 gold badges39 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Jeremy Thompson · Accepted Answer · 2012-11-01 05:43:25Z

1

Dim uniqueName as String = "A_NamedRangeName_" & i
Dim rnArea as Range = .ActiveSheet.Range(leftCol & startRow, rightCol & endRow)
Name name = .ActiveBook.Names.Add(uniqueName, rnArea)

Name Range names cannot:
- start with digits
- include , : ; characters
- be duplicate names, each must have a unique name
- be a reserved excel function name

edited Nov 1, 2012 at 5:43

answered Nov 1, 2012 at 5:35

Jeremy Thompson

66.3k38 gold badges225 silver badges344 bronze badges

Collectives™ on Stack Overflow

Name a range in dynamically created excel sheet using VB.NET

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related