CODEWITHSUNDEEP
vbaexcel
CA_CA
CA_CA

Reputation: 143

Assign names to spreadsheets

I'm trying to create multiple sheets and assign them predefined names based on a range established in the same worksheet.

For example, I have the following values for range (A1:A3) in sheet "Names":

Test1
Test2
Test3

I want to create new sheets in the same worksheet named "Test1","Test2" and "Test3"

I'm using the command below in a loop but I get an error:

Sheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.Name = CStr(Range("A2"))

Upvotes: 0

Views: 71

Answers (2)

Tirma
Tirma

Reputation: 674

The code of Gary's Student works perfectly. I can only imagine 2 kind of errors:

  1. The sheet Names does not exists

  2. One of the sheets on range A1:A3 already exists

With this code, you can check where is your problem:

Sub SheetAddre()
   Dim a As Range
    If (Not SheetExists("Names")) Then
        MsgBox ("The sheet called Names does not exists")
        Exit Sub

    End If
   For Each a In Sheets("Names").Range("A1:A3")

        If (SheetExists(a.Value)) Then
            MsgBox ("The sheet called" + a.Value + " already exists")
            Exit Sub

        End If


      Sheets.Add after:=Sheets(Sheets.Count)
      ActiveSheet.Name = a.Value
   Next a
End Sub


Function SheetExists(n) As Boolean
  SheetExists = False
  For Each ws In Worksheets
    If n = ws.Name Then
      SheetExists = True
      Exit Function
    End If
  Next ws
End Function

Upvotes: -1

Gary's Student
Gary's Student

Reputation: 96753

You must refer to each name in a loop:

Sub SheetAddre()
   Dim a As Range

   For Each a In Sheets("Names").Range("A1:A3")
      Sheets.Add after:=Sheets(Sheets.Count)
      ActiveSheet.Name = a.Value
   Next a
End Sub

Upvotes: 3

Related Questions