The line code with the underscore's below fails when i click my command button but runs from the VBA screen. Also i added the underscores to highlight which line i am having trouble with not part of the code.
Help please
'Make sure that cell B4 is not empty
If IsEmpty(Range("E2").Value) Then
MsgBox "Part Number and Operation Number Required!", vbCritical
Exit Sub
End If
'Assign the new name to the Sheet'
newshtname = Range("E1").Value & " " & (Range("E2").Value)
'make sure that the proposed name does not already exist
For Each Sheet In ActiveWorkbook.Sheets
If UCase(Sheet.Name) = UCase(newshtname) Then
MsgBox "The sheet name you have input already exists. Please input a new name.", vbCritical
Exit Sub
End If
Next Sheet
'the following will copy the active sheet, place the copy after the first sheet in the workbook, and then rename the sheet to the name that was input in E2.
Dim oSh As Worksheet
______Worksheets("Master").Copy after:=Worksheets(Worksheets.Count)
Set oSh = ActiveSheet______
ActiveSheet.Name = newshtname
'this clears Master sheet User Inputs'
Worksheets("Master").Select
ActiveWindow.ScrollRow = 1
Range("A4:Q250").Select
Selection.ClearContents
End Sub
Answers & Comments
Verified answer
What is the error you get?
It worked for me
How are you doing things?
I've just run the code and it works fine. What I had to do, in addition, was to create a separate macro to open the userform then pressing the command button created a new sheet with the name that was in E2
This was in a module created in Book1.xlsm
Sub openform()
UserForm1.Show
End Sub
and running the openform macro showed the form. Have the userform1 ShowModal as False otherwise you can't get to the sheets.
Why do you set oSh when you do not use it?