Loop will not work

[Defclone]

Guys

Im new to VBA but Im currently working on a model that generates some statements for customers based on revenue and market share etc. The name of the customers change from month to month, I have a sheet where I dump the data and the model pulls all the data from this sheet. Since there are some 40 or so sheets I wanted dynamic tab names, I pieced together some code but it seems that the Next loop will not work. I can get the macro to work for the active sheet but not for the whole workbook. It can be the code or maybe I messed something up when I assigned a control-button. I would like the tab name to be "I1" since this cell corresponds to a name in a list in the input data-sheet
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
Sub FANE_NAVNEIII()<o></o>
Dim ws As Worksheet<o></o>
<o></o>
For Each ws In ActiveWorkbook.Sheets<o></o>
Set Target = Range("I1")<o></o>
If Target = "" Then Exit Sub<o></o>
On Error GoTo Badname<o></o>
ActiveSheet.Name = Left(Target, 31)<o></o>
Exit Sub<o></o>
Badname:<o></o>
MsgBox "Please revise the entry in A1." & Chr(13) _<o></o>
& "It appears to contain one or more " & Chr(13) _<o></o>
& "illegal characters." & Chr(13)<o></o>
Range("I1").Activate<o></o>
<o></o>
Next ws<o></o>
<o></o>
End Sub<o></o>
<o> </o>
<o> </o>
I also need a macro that saves each sheet as a separate workbook with values only using the tab name for file name.<o></o>
<o> </o>
Home some one can help as my deadline is closing in J

 

[VoG]

Try like this

Sub FANE_NAVNEIII()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Sheets
    With ws
        .Name = Left(.Range("I1").Value, 31)
    End With
Next ws
End Sub

 

[Defclone]

Hey VoG

Thanks a bunch drinks are on me Just trying to understand what I did wrong, guess I did not reference the ws right?