use array to add and name new sheets

Question

I need to create a given number of sheets, depending on the amount of names in a List. The sheets need to have the same name as the string value in every cell inside the list/array. So far my code, which is not working:

Private Sub loopfilter()

Dim VersandRange As Range
Dim rng As Range
Dim Name As String

Set VersandRange = Range("J2", Cells(Rows.Count, "j").End(xlUp))

    For Each rng In VersandRange

            rng.Value = Name
            Worksheets.Add
            ActiveSheet.Name = Name
    Next
end sub

Thanks!

One line is reversed Name=rng.Value

Gary's Student
– Gary's Student

2017-03-17 11:04:33 +00:00
Commented Mar 17, 2017 at 11:04 — Gary's Student
– Gary's Student, Commented Mar 17, 2017 at 11:04
or just Worksheets.Add.Name = rng.Value

Slai
– Slai

2017-03-17 11:44:02 +00:00
Commented Mar 17, 2017 at 11:44 — Slai
– Slai, Commented Mar 17, 2017 at 11:44

CLR · Accepted Answer · 2017-03-17 11:04:47Z

2

Would this not work?

For Each rng In VersandRange
    Worksheets.Add
    ActiveSheet.Name = rng.Value
Next

answered Mar 17, 2017 at 11:04

CLR

12.3k1 gold badge13 silver badges31 bronze badges

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

4 Comments

Slai Over a year ago

@Urumita sheet names are limited to 31 characters and can't contain any of those 7 characters \/*[]:? accountingweb.com/technology/excel/…

Urumita Over a year ago

Thank you slai, but it is not the case. Im using none of those characters and the maximum =Long I have is 22

CLR Over a year ago

You've only swapped out the For.. Next part of your code with mine, yes?

CLR Over a year ago

What is the rest of the error generated under code 1004?

Collectives™ on Stack Overflow

use array to add and name new sheets

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related