Hi Experts,
In need of your help as usual. I have a manually triggered code to manually create a new worksheet by manually entering the sheet name and the code checks for duplicates, copies master template sheet and renames it to newly entered name.
Now, I need a batch processing vba code to look at my names list in a range e.g A:A take each value, trigger my macro and enter the referenced name into it then letting the macro create a sheet and name it and then move on to the next name on the list.
This is my current macro code to add a new sheet:
Sub addnewsheetbutton()
' addnewsheetbutton Macro
'First, jump through the validation hoops
Dim AddSheetQuestion As Variant
'Define the application input box question
showAddSheetQuestion:
Dim QuestionText As String
Dim ValidName As Boolean
QuestionText = "Please enter the name of the sheet you want to add," & vbCrLf & "or click the Cancel button to cancel the addition:"
Do
AddSheetQuestion = Application.InputBox(QuestionText, _
"What sheet do you want to add?")
If AddSheetQuestion = False Then
MsgBox "You clicked the Cancel button." & vbCrLf & "No new sheet will be added.", 64, "Cancel was clicked."
Exit Sub
End If
ValidName = InvalidName(AddSheetQuestion)
QuestionText = "You entered an Invalid sheet name. Please try again."
Loop Until (ValidName = False)
'See if a worksheet exists that is named as the new name being attempted to add.
'We want this code to error, because if it does, it will mean no such sheet exists
'so we can complete this macro.
On Error Resume Next
Worksheets(UCase(AddSheetQuestion)).Activate
If Err.Number <> 9 Then
Err.Clear
GoTo ErrorHandler1
End If
'Here's the actual sheet addition code
With Application
.ScreenUpdating = False
.EnableEvents = False
.DisplayAlerts = False
End With
'Make the Template sheet visible, and copy it
With Worksheets("template")
.Visible = xlSheetVisible
.Activate
End With
'Copy template sheet
Sheets("template").Copy After:=Worksheets(Worksheets.Count)
NewPageName = AddSheetQuestion
ActiveWindow.ActiveSheet.Name = NewPageName
Sheets("template").Visible = False
With Application
.GoTo Range("B12"), True
End With
'Hide Template sheet
Worksheets("template").Visible = xlSheetVeryHidden
With Application
.ScreenUpdating = True
.EnableEvents = True
.DisplayAlerts = True
End With
'Inform the user the macro is completed
MsgBox "The new sheet name ''" & AddSheetQuestion & "'' has been added.", 64, "Sheet addition successful."
Exit Sub
'If a worksheet exists named with the same three-character code
ErrorHandler1:
MsgBox "A worksheet already exists that is named " & AddSheetQuestion & "." & vbCrLf & vbCrLf & _
"Please click OK, verify the name you really" & vbCrLf & _
"want to add, and try again." & vbCrLf & vbCrLf & "Sheet addition cancelled.", 48, "Sorry, that name already taken."
Exit Sub
End Sub
Public Function InvalidName(ByVal SheetName As String) As Boolean
With CreateObject("VBScript.RegExp")
.IgnoreCase = True
.Global = True
.Pattern = "[\[/\?\]\*()]"
InvalidName = .test(SheetName)
End With
End Function
Thanks in advance...
Bookmarks