I need code that loops to see if...Please Help

Posted by steve on June 06, 2001 8:20 AM

I have a multi-user workbook. On the front page i have a checklist. The user enters yes or no into a the fields that the user wants to use. Then i want a done button at the bottom. I need the code for vb that will check the cells and where a yes is enterd, so that it opens NEW pages with each yes and that it renames the pages to what the user enterd yes on. I dont know very much about VB, if someone could give me the code I would be very greatful. The cells that have yes or no would be o6-o22, and names in n6-n22. Thanks

Posted by Barrie Davidson on June 06, 2001 9:01 AM

I incorporated a check for duplicate sheet names as well.

Sub Insert_Sheets()
'Written by Barrie Davidson
Dim New_Sheet_Name As String
Dim Input_Sheet As String

Range("O6").Select
Input_Sheet = ActiveSheet.Name
Do Until ActiveCell.Row > 22
If Selection.Value = "Yes" Then
New_Sheet_Name = ActiveCell.Offset(0, 1).Value
For Each ws In Worksheets
Worksheet_Loop:
If ws.Name = New_Sheet_Name Then
ActiveCell.Offset(0, 1).Value = InputBox("Duplicate Sheet Name Entered" & Chr(13) & "Enter New Sheet Name")
New_Sheet_Name = ActiveCell.Offset(0, 1).Value
GoTo Worksheet_Loop
Else
End If
Next ws
Application.Worksheets.Add after:=Worksheets(Input_Sheet)
ActiveSheet.Name = New_Sheet_Name
Application.Worksheets(Input_Sheet).Select
ActiveCell.Offset(1, 0).Select
Else
ActiveCell.Offset(1, 0).Select
End If

Loop

End Sub

Regards,
Barrie

Posted by steve on June 06, 2001 9:19 AM

Error 'ActiveSheet.Name = New_Sheet_Name'

Loop

Thanks,
But i get an error on ActiveSheet.Name = New_Sheet_Name It stops the macro on a blank page with the sheet1 name as defalt. How do i fix this problem?

Posted by Barrie Davidson on June 06, 2001 10:17 AM

Re: Error 'ActiveSheet.Name = New_Sheet_Name'

Loop

Steve,
Sorry (my fault) change:
New_Sheet_Name = ActiveCell.Offset(0, 1).Value
to:
New_Sheet_Name = ActiveCell.Offset(0, -1).Value

Note that this line occurs twice in the code. It was incorrectly looking at column P for the sheet name instead of column N.

Maybe I should read the problem a little closer!!

Barrie