Create Worksheet from a list error

[Mattastic]

I need some help with my code.

Even though after initial issue I have changed data resulting from a lookup to a value only list, the code tries to create a new worksheet and then fails on the first blank in the list. I cant work out why

Sub CreateSheetFromList()
' Cut & Paste Short Names to remove error in tab creation macro
Range("C8:C53").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

' Create Tab and copy Template into the tab based on SETUP list
Dim MyCell As Range, MyRange As Range
Set MyRange = Sheets("SETUP").Range("c8")
Set MyRange = Range(MyRange, MyRange.End(xlDown))
For Each MyCell In MyRange
Sheets.Add after:=Sheets(Sheets.Count) 'creates a new worksheet
Sheets(Sheets.Count).Name = MyCell.Value 'renames the new worksheet
Worksheets("Template").Cells.Copy ActiveSheet.Range("A1") 'copies Template to new worksheet
Next MyCell

End Sub

 

[Norie]

You don't seem to be checking for blanks, and you might MyRange might not be what you thing it is.

Try this.

Option Explicit
Sub CreateSheetFromList()
Dim wsNew As Worksheet
Dim MyCell As Range, MyRange As Range
Dim LastRow As Long

    ' Cut & Paste Short Names to remove error in tab creation macro
    With Range("C8:C53")
        .Copy
        .PasteSpecial Paste:=xlPasteValues
    End With
    
    LastRow = Sheets("SETUP").Range("C" & Rows.Count).End(xlUp).Row
    Set MyRange = Sheets("SETUP").Range("C8:C" & LastRow)
    For Each MyCell In MyRange.Cells
        Set wsNew = Sheets.Add(After:=Sheets(Sheets.Count))
        wsNew.Name = MyCell.Value    'renames the new worksheet
        Worksheets("Template").Cells.Copy wsNew.Range("A1")    'copies Template to new worksheet
        
    Next MyCell
End Sub

[/code]

 

[Mattastic]

Hi Norie,

Thnaks for your suggestion. But still getting the issue at same point?

For Each MyCell In MyRange.Cells
Set wsNew = Sheets.Add(After:=Sheets(Sheets.Count))
wsNew.Name = MyCell.Value 'renames the new worksheet
Worksheets("Template").Cells.Copy wsNew.Range("A1") 'copies Template to new worksheet
​

A new worksheet is created and then tries to rename, although there is no new name in list???

 

[Norie]

Do you get any error message?

 

[Mattastic]

Does this help?

Run-time error '1004':

Method 'Name of object '_Worksheet' failed

 

[Norie]

Are all the names valid, and unique?

 

[Mattastic]

As far as I am aware, Yes.

Is there a way to qualify this?

 

[Mattastic]

I been playing around this morning and recreated the range elsewhere; it works fine as a list of static data, but if I try to create as result of a formula;

=IFERROR(VLOOKUP(B8,CODES!C3:D134,2,FALSE),"")

this introduces the issue, I guess it is reading the formula as content, but of course the result is " ".

I added the copy, paste special (Values) code to break this initialy, but that doesnt work - any ideas what I am missing?

 

[Norie]

If the result of the formula is "" copying and pasting values isn't going to change that.