On Monday, April 8, 2013 12:26:38 PM UTC-7, GS wrote:
Thanks, Claus! You're absolutely correct! (I got lazy<g, my bad)...



Sub CopySheetAndNameCopies_v2()

Dim vNames, sFormulas, n&, k&

vNames = Sheets("Summary").Range("BreakdownList")



Application.ScreenUpdating = False

Sheets("Main Swb").Visible = True



For n = LBound(vNames) To UBound(vNames)

If not bSheetExists(vNames(n, 1)) Then

Sheets("Main Swb").Copy after:=Sheets("Summary")

ActiveSheet.Name = vNames(n, 1)

sFormulas = "='" & vNames(n, 1) & "'!G7"

Sheets("Summary").Range("BreakdownList").Cells(n). Offset(,

1).Resize(1, 4) = sFormulas

End If 'Not bSheetExists

Next 'n



Sheets("Main Swb").Visible = False

Application.ScreenUpdating = True

End Sub



Sub CopySheetAndNameCopies_v3()

Dim vNames, vFormulaRefs, vaFormulas(1, 4)

Dim n&, k&

vNames = Sheets("Summary").Range("BreakdownList")

vFormulaRefs = Array("G7", "H7", "I7", "J7")



Application.ScreenUpdating = False

Sheets("Main Swb").Visible = True



For n = LBound(vNames) To UBound(vNames)

If not bSheetExists(vNames(n, 1)) Then

Sheets("Main Swb").Copy after:=Sheets("Summary")

ActiveSheet.Name = vNames(n, 1)

For k = 1 To 4

vaFormulas(1, k) = "='" & vNames(n, 1) & "'!" & vFormulaRefs(k)

Next 'k

Sheets("Summary").Range("BreakdownList").Cells(n). Offset(,

1).Resize(1, UBound(vaFormulas, 2)) = vaFormulas

End If 'Not bSheetExists

Next 'n



Sheets("Main Swb").Visible = False

Application.ScreenUpdating = True

End Sub



Function bSheetExists(WksName) As Boolean

On Error Resume Next

bSheetExists = CBool(Len(ActiveWorkbook.Sheets(WksName).Name))

End Function



--

Garry

I tried both these subs and as far as I can tell, they do everything needed, testing for already incorporated sheet and doing nothing if no sheet new name is added.

The function is puzzling to me. Can't read what it does.

My next task is to write some code that will delete the sheets that have been entered. I have a little snippet that does that now but it is contained to the generic four names I've been testing with. So I need to give some thought on how I get the myrid of names entered to make sheet for to a "bucket" so I can dump them en-mass if or when needed. Some names will be around for some time and others will come and go at a pace yet to be determined.. The names in column C will be duck soup but deleting a sheet that has the same name as column C, I will have to burn some thought calories. I may be back for help on this but for now I'm off and thinking. As I am typing this it occurs to me maybe I only want to dump a few of the sheets and name not all. Thep plot thickens

Sure do like the codes.

Thanks.

Howard