Attn: Help with code please! Code GURUS!


Posted by Steve on June 08, 2001 1:28 PM

I donn't know anything much about vb, Barrie Davidson wrote this code for me and i need some things added to it. You all have been very helpful, Thanks.

I have a button "done" (see code below), It refrences the cells o6-o24, to check for yes. When a yes is selected, and 'done' is pressed. I need it to copy 2 sheets(Engineering Assumptions, and Engineering Worksheet) within the book. And rename these thoes two names along with a "-" and the cell next to the "Yes" which are cells n6-n24. This Would be GREATLY apreciated if this could be done,
--Thanks.. Here is the code:

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


Posted by Barrie Davidson on June 08, 2001 2:13 PM


Hi Steve, I changed the code as per your parameters.

Sub Insert_Sheets2()
'Written by Barrie Davidson
Dim New_Sheet_Name As String
Dim New_Sheet_Suffix As String
Dim Input_Sheet As String
Dim Sheet_Count As Integer

Range("O6").Select
Input_Sheet = ActiveSheet.Name
Do Until ActiveCell.Row > 22
If Selection.Value = "Yes" Then
New_Sheet_Suffix = "-" & ActiveCell.Offset(0, -1).Value
Sheet_Count = ActiveWorkbook.Sheets.Count
Sheets("Engineering Assumptions").Copy after:=Sheets(Sheet_Count)
New_Sheet_Name = "Engineering Assumptions" & New_Sheet_Suffix
ActiveSheet.Name = New_Sheet_Name
Sheet_Count = ActiveWorkbook.Sheets.Count
Sheets("Engineering Worksheet").Copy after:=Sheets(Sheet_Count)
New_Sheet_Name = "Engineering Worksheet" & New_Sheet_Suffix
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

Let me know if this works okay for you.

Regards,
Barrie



Posted by Steve on June 08, 2001 2:35 PM

Your Awsome...Thanks!