Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.
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
Join the community of 500,000 technology professionals and ask your questions.
Connect with top rated Experts
6 Experts available now in Live!