Results 1 to 3 of 3
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts

    Macro needed for new sheet (2002+)

    I need a macro that I can then turn into a button in Excel that will copy an existing sheet (being used as a template) to the end of the workbook, prompt for a new name and then label the new sheet tab. Next to no VBA abilities here...for Hans and others you can do this in your sleep it seems. -- TIA...

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Macro needed for new sheet (2002+)

    Here is such a macro:

    Sub CopySheet()
    Dim strName As String
    Dim wsh As Worksheet
    Dim blnExists As Boolean

    On Error GoTo ErrHandler

    Do
    blnExists = False
    strName = InputBox("Enter name for new sheet", , "New Sheet")
    If strName = "" Then Exit Sub
    For Each wsh In ThisWorkbook.Worksheets
    If wsh.Name = strName Then
    MsgBox "The name '" & strName & "' is in use." & vbCrLf & _
    "Please select another one.", vbExclamation
    blnExists = True
    Exit For
    End If
    Next wsh
    Loop Until blnExists = False

    ThisWorkbook.Worksheets("TemplateSheet").Copy _
    After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksh eets.Count)
    ThisWorkbook.ActiveSheet.Name = strName
    Exit Sub

    ErrHandler:
    MsgBox Err.Description, vbExclamation
    End Sub

    Replace TemplateSheet with the name of the sheet you want to copy.

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts

    Re: Macro needed for new sheet (2002+)

    Fantastic. Much appreciated, as always!

Posting Permissions

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