Private Sub cmdAddSheets_Click() 'dim variables Dim x As Integer Dim y As Integer Dim z As Integer 'set error handler On Error GoTo Copier_Error 'turn off screen updating and calculation Application.ScreenUpdating = False Application.Calculation = xlCalculationManual 'check if the workbook isshared and exit if it is If ThisWorkbook.MultiUserEditing Then MsgBox "Please Unshare the workbook before you proceed" Exit Sub End If 'check that all values are in the userform If Me.txtName = "" Or Me.cboNum = "" Or Me.cboNumSt = "" Then MsgBox "You forgot to add the parematers for the sheet name." Exit Sub End If 'protect_all Protect_All 'set variables for number of sheet x = Me.cboNum.Value 'set variable for sheet name prefix y = Me.cboNumSt.Value z = x + y – 1 'check the first sheet name NewName = Sheet1.Range("A4") 'loop through to check For Each sh In ThisWorkbook.Worksheets If sh.Name = NewName Then MsgBox "This information already exists.You are not permitted to copy over it" 'if sheet name exists then got to end of sub GoTo Copier_Error: End If Next 'run loop to add sheets For numtimes = y To z Sheet3.Copy after:=Worksheets(Worksheets.Count) 'create the new sheet name ActiveSheet.Name = Me.txtName & numtimes Next 'return to Interface sheet Sheet1.Select 'list workshhet names on the interface ListWorkSheetNames 'turn of screen updating and calculation Application.ScreenUpdating = True Application.Calculation = xlCalculationAutomatic 'error handler On Error GoTo 0 Exit Sub Copier_Error: 'message if sheet already exists MsgBox "I think " & NewName & " already exists. Check the starting number" End Sub
Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.
When asked, what has been your best career decision?
Deciding to stick with EE.
Being involved with EE helped me to grow personally and professionally.
Connect with Certified Experts to gain insight and support on specific technology challenges including:
We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE