how do i chk is sheet exists my attempt included

[andy]

hi
I have a list of sheet name in a1:a10
and what I want to do is go through each cell in that range and chk if a
work sheet exists with that name
if it does not, create a sheet in that name and them move on to the next
cell in the list
this is what i have so far

can some one point me in the right direction

Dim scv As Range
Set scv = ActiveSheet.Range("a1:a10")
Dim rng As Range
For Each rng In scv
If rng.Value > 0 Then
Dim wks_Sht As Worksheet

For Each wks_Sht In Workbooks(ActiveWorkbook.Name).Worksheets
If wks_Sht.Name = rng.Value Then

Exit For
Else
If wks_Sht.Name <> rng.Value Then
Sheets.Add.Name = rng.Value
Exit For
End If
End If


Next wks_Sht
End If
Next rng

End Sub

 

[Guest]

What you have is mighty close... Give this a try...

Sub AddSheets()
Dim wks As Worksheet
Dim rngCurrent As Range
Dim rngToSearch As Range

Set wks = Sheets("Start")
Set rngToSearch = wks.Range("A1:A10")

On Error GoTo AddSheet
For Each rngCurrent In rngToSearch
If rngCurrent.Value <> Empty Then _
Set wks = Sheets(rngCurrent.Value)
Next rngCurrent
On Error GoTo 0

Exit Sub
AddSheet:
Set wks = Sheets.Add
wks.Name = rngCurrent.Value
Resume Next
End Sub

 

[bhofsetz]

Give this modification to your code a try:


Code:
--------------------
Sub ChgSheets()
Dim scv As Range
Set scv = ActiveSheet.Range("a1:a10")
Dim rng As Range
Dim wksFound As Boolean
For Each rng In scv
wksFound = False
If rng.Value > 0 Then
Dim wks_Sht As Worksheet

For Each wks_Sht In Workbooks(ActiveWorkbook.Name).Worksheets
If wks_Sht.Name = rng.Value Then
wksFound = True
Exit For
End If
Next wks_Sht
If wksFound = False Then
Sheets.Add.Name = rng.Value
End If
End If
Next rng

End Sub
--------------------


You could also use error handling to check for the existence of each
sheet name in your range and then create the sheet if it doesn't
exist.

HTH

 

[andy]

brilliant thankyou

What you have is mighty close... Give this a try...

Sub AddSheets()
Dim wks As Worksheet
Dim rngCurrent As Range
Dim rngToSearch As Range

Set wks = Sheets("Start")
Set rngToSearch = wks.Range("A1:A10")

On Error GoTo AddSheet
For Each rngCurrent In rngToSearch
If rngCurrent.Value <> Empty Then _
Set wks = Sheets(rngCurrent.Value)
Next rngCurrent
On Error GoTo 0

Exit Sub
AddSheet:
Set wks = Sheets.Add
wks.Name = rngCurrent.Value
Resume Next
End Sub