how do i chk is sheet exists my attempt included

  • Thread starter Thread starter andy
  • Start date Start date
A

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
 
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
 
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
 
brilliant thankyou



Jim Thomlinson said:
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
Click to expand...
 
Back
Top