Looping Two Variables..Getting an Error..Invalid Next Control Variable Reference

[peee2]

Hi All.. Im new to Macros..so getting an error..the macro has to basically check whether there exists a worksheet with a certain name (the name is in BF column) if it exists den do nothing. if does not exist den create a copy of an existing worksheet and rename it with name in BF column.Repeat this for all the rows in BF column.
what changes should be done in the below macro to work properly. pls help

Sub CopySheetAndRename()

For i = 1 To Worksheets.count
For r = 1 To Rows.count
If Worksheets(i).Name = Range("BF" & r + 1) Then
exists = True
End If
Next i


If Not exists Then

Dim ws As Worksheet
Set ws = ActiveSheet
Worksheets("CopySheet").Copy After:=Worksheets("Misc")
If ws.Range("BF" & r + 1).Value <> "" Then
On Error Resume Next
ActiveSheet.Name = ws.Range("BF" & r + 1).Value

End If
Next r
ws.Activate
End If

End Sub

Thanks in Advance

 

[Michael M]

you need to loop through the rows before the sheets...
so, the first Next statement needs to be r !!
AND
It would help you if you got into the habit of declaring ALL of your variables

Sub CopySheetAndRename()
[color=red]dim r as long, i as long,ws as worksheet[/color]
For i = 1 To Worksheets.count
For r = 1 To Rows.count
If Worksheets(i).Name = Range("BF" & r + 1) Then
exists = True
End If
Next [color=red]r[/color]


If Not exists Then
Set ws = ActiveSheet
Worksheets("CopySheet").Copy After:=Worksheets("Misc")
If ws.Range("BF" & r + 1).Value <> "" Then
On Error Resume Next
ActiveSheet.Name = ws.Range("BF" & r + 1).Value

End If
Next [color=red]i[/color] 
ws.Activate
End If
End Sub

 

[steve the fish]

Theres allsorts wrong there im afraid. What is the name of the sheet where the list of sheet names appears?

 

[peee2]

Thanks Micheal for the quick response. Tried ur macro. now getting a different error mssg..Compile error: Next without For.

 

[peee2]

Active sheet that is

 

[steve the fish]

Id recommend using the name of the sheet as it alters the action of the macro if you happen to run it when the 'wrong' sheet is active. That said this will work if you have the sheet with the list of names active:

Function SheetExists(sh As String) As Boolean

Dim mySheet As Worksheet

On Error Resume Next
Set mySheet = Worksheets(sh)

If Not mySheet Is Nothing Then
    SheetExists = True
Else
    SheetExists = False
End If

End Function

Sub CopySheetAndRename()

Dim lr As Long, i As Long

With ActiveSheet
    lr = .Range("BF" & .Rows.Count).End(xlUp).Row
    For i = 2 To lr
        If Not SheetExists(.Range("BF" & i).Value) Then
            Worksheets("CopySheet").Copy After:=Worksheets("Misc")
            ActiveSheet.Name = .Range("BF" & i).Value
        End If
    Next
End With

End Sub

 

[peee2]

Great. Did what you said and it worked perfectly. Many a thanks Steve and Best wishes. Grateful to you & Mr Excel.