+ Reply to Thread
Results 1 to 3 of 3

vba macro to batch process adding worksheets

Hybrid View

  1. #1
    Registered User
    Join Date
    04-14-2015
    Location
    London
    MS-Off Ver
    2007
    Posts
    36

    vba macro to batch process adding worksheets

    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...

  2. #2
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: vba macro to batch process adding worksheets

    Maybe:

    Sub SKooLZz()
    Dim i As Long
    Dim ws As Worksheet
    Set ws = ActiveSheet
    With ws
    For i = 2 To .Range("A" & Rows.Count).End(3).row
        On Error Resume Next
        Sheets.Add after:=Sheets(Sheets.Count)
        ActiveSheet.name = .Cells(i, "A").Value
    Next i
    End With
    End Sub

  3. #3
    Registered User
    Join Date
    04-14-2015
    Location
    London
    MS-Off Ver
    2007
    Posts
    36

    Re: vba macro to batch process adding worksheets

    Many thanks JOHN H. DAVIS... your code worked a treat.... and I've customised it to my activity.

    Just 1 more additional cry for help, how do I amend the code to first check if the sheet with the same name exists and if so skip the addition and move to the next. This is my code which is copying and renaming a master template:

    Sub batchmaker()
    Dim i As Long
    Dim ws As Worksheet
    Dim Newpagename As String
    Set ws = ActiveSheet
    With ws
    For i = 2 To .Range("A" & Rows.Count).End(3).Row
        On Error Resume Next
        
    'Make the Template sheet visible, and copy it
    Worksheets("template").Visible = xlSheetVisible
    Worksheets("template").Activate
    
    'Copy template sheet
    Sheets("template").Copy After:=Worksheets(Worksheets.Count)
    Newpagename = .Cells(i, "A").Value
    ActiveWindow.ActiveSheet.Name = Newpagename
    Sheets("template").Visible = False
    
    'Hide Template sheet
    Worksheets("template").Visible = xlSheetVeryHidden
    
    Next i
    End With
    End Sub
    Oh. I could also do with code to reverse this and batch search and delete sheets. I know I ask for too much but I'm a newbie to vba it may take me 3 hours to work out how to doe it when experts take 3-5 so your help is greatly appreciated.


    Thanks in Advance..

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Adapt macro to batch process files
    By beng404 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-27-2012, 07:09 PM
  2. VBA Macro to process duplicate phone numbers in 3 worksheets
    By delforum in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 02-07-2011, 11:02 AM
  3. Reverse Geocode Batch Process
    By ernestgoh in forum Excel General
    Replies: 0
    Last Post: 01-19-2010, 03:35 PM
  4. Batch renaming of worksheets
    By Lumen S in forum Excel General
    Replies: 1
    Last Post: 08-17-2006, 03:40 PM
  5. [SOLVED] Can I batch rename new worksheets
    By Harry Limey in forum Excel - New Users/Basics
    Replies: 3
    Last Post: 01-19-2005, 06:06 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1