My skills aren't good enough to pull this off

[jwindholz2725]

Ok where to start. I have a excel book with two sheets. For references one is called "WBS" and the other is "Template".

-The WBS tab has a list of names on it that change for each project. Sometimes names are in A1:A20 and other times A1:A30.
-The Template tab is basically the format that I copy and build tabs off of. It never changes

Ok so the goal here is for a code to pull from the names on the "WBS" tabs cells A1:A20 and create copies of the "template" tab and name it with the names in "WBS" A1:A20.

The issue is A1:A20 change depending on project. So idealy the code would read A1:A200....and any cell with no name would not create a tab named after it.

I have code to create tabs off names of WBS A1:A20 and code to make copies off the Template tab. But I can't combine both. Is this possible???? Please help!!

 

[HalfAce]

Is this possible?

Absolutely.
How do you want to handle duplicate entries in A1:A20 (30 or whatever)?
Can't make sheets with duplicate names...

 

[Geoff Taylor]

<code>
Sub MakeTemplates()

Dim rMyCell As Range

For Each rMyCell In Sheets("WBS").Range("A1:A" & Range("a1").End(xlDown).Row)
Sheets("Template").Copy Before:=Sheets(1)
Sheets("Template (2)").Name = rMyCell.Value
Next
End Sub

</code>

The above code should work. It will get upset with duplicate names or non-standard characters.

 

[jwindholz2725]

Well A1:A20 will always have different names. The only issue I would run into is for example A5,A8, and A19 for instance may be blank cells. So I'm hoping theres something for no data, no tab gets created. Other than that no duplicates.

Thanks

 

[jwindholz2725]

That worked great Geoff. The only problem is if there is a blank in one of the cells. it will stop. Is there a way to run through the blanks like A1:A200 but it does not create a tab for the blanks?

Thank You

 

[Rick Rothstein]

That worked great Geoff. The only problem is if there is a blank in one of the cells. it will stop. Is there a way to run through the blanks like A1:A200 but it does not create a tab for the blanks?

I have not tested this, but I think it should do what you asked (it is Geoff's code modified)...

Sub MakeTemplates()
  Dim rMyCell As Range
  For Each rMyCell In Sheets("WBS").Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row)
    If Len(rMyCell.Value) Then
      Sheets("Template").Copy Before:=Sheets(1)
      Sheets("Template (2)").Name = rMyCell.Value
    End If
  Next
End Sub

 

[jwindholz2725]

That did it! Thanks Rick and Geoff!! You guys are awesome I appreciate it.

Thanks!!!!