Using variables to name or rename worksheets

[chazrab]

How can I use a variable to assign a new worksheet a new name or rename it? I have a list in col A of 200 names that need to be assigned to 200 new worksheets. Using a For Each loop with the value of each cell in col A being assigned as a new sheet name is as simple as I can desribe it. The code below is as far as I have gotten. "x" just puts the letter x in the sheet name. I want a varible routine to grab each cell value and assign it to a new worksheet. Can somebody help me???

Private Sub CommandButton2_Click()
For Each c In Worksheets("All Wells").Range("A1:A169").Cells
If c.Value = "" Then
Exit For
Else
c.Rows.Select
ActiveCell.Select
Selection.Copy
Set NewSheet = Worksheets.Add
NewSheet.Name = "x"

End If
Next
End Sub[/b]

 

[Richard Schollar]

Hi Chazrab

Welcome to the Board!

Maybe:

Private Sub CommandButton2_Click() 
Dim i as integer, ws as Worksheet
With Worksheets("All Wells")
For i = 1 To .Range("A1:A169").Cells.Count
   Set ws = Sheets.Add
   ws.Name = .Cells(i,1).Value
Next
End With
End Sub

 

[HalfAce]

Hello chazrab, welcome to the board.
There are a couple of things about your post that raise some questions for me.
1) You say you have a list of 200 names, yet you're only looping through 169 cells in column A.
2) Your example code tests for blanks. Does this mean the list may not reach down as far as
expected, or that there may be blanks within the list somewhere?
3) As it's written, it's a one time shot. You can't run it again to add sheets for new names added
to the list. Is this your intent?
4) What about the possibility of any duplicate names in the list. You'll run into the same problem
you would with trying to run the code more than one time.

Are any of these concerns ever going to be an issue?

 

[HalfAce]

Hello chazrab,
In response to your PM, here's something you might want to give a try.
It assumes the list of sheet names is in column A of sheet 1. (Change that to suit)
It allows duplicate names and/or adding new names to the list and the code to be
run repeatedly.
It has the sleep time set for 1000 milliseconds, change that to suit as well.

Option Explicit
Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

Sub AddSheets()
Dim LstRw As Long, _
    ThisRw As Long, _
    WsList As Object, _
    Ws As Worksheet
    Set WsList = CreateObject("Scripting.Dictionary")

For Each Ws In ThisWorkbook.Worksheets
  If Not WsList.Exists(Ws.Name) Then WsList.Add Ws.Name, Nothing
Next Ws

With Sheets("Sheet1")
  LstRw = .Cells(Rows.Count, "A").End(xlUp).Row
  For ThisRw = LstRw To 1 Step -1
    If Not WsList.Exists(.Cells(ThisRw, "A").Value) Then
      Sheets.Add.Name = .Cells(ThisRw, "A").Value
      Sleep (1000)
    End If
  Next ThisRw
End With

Set WsList = Nothing

End Sub