Button to copy sheet, rename sheet sequencially.

Discussion in 'Microsoft Excel Programming' started by foxgguy2005, Jun 14, 2005.

  1. foxgguy2005

    foxgguy2005 Guest

    I have a Setup sheet, which has a button to creat a new sheet, which
    actually copies a tamplet. When i click this button it calls the new
    tab "Tamplet (2)"
    Is there a sub i can put in the copy routine to auto-rename the tabs
    sequencially?
    Note number of tabs is based upon how many times the user clicks the
    new page button.

    Thanks!
    ~Josh


    --
    foxgguy2005
    ------------------------------------------------------------------------
    foxgguy2005's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=23663
    View this thread: http://www.excelforum.com/showthread.php?threadid=378885
     
    foxgguy2005, Jun 14, 2005
    #1
    1. Advertisements

  2. foxgguy2005

    Guest Guest

    Option Explicit

    Public Sub AddSheets()
    Dim ws As Worksheet
    Set ws = Worksheets("template")
    ws.Copy Worksheets(1)
    SetSheetName Worksheets(1)
    End Sub
    Private Sub SetSheetName(ws As Worksheet)
    On Error Resume Next
    Dim sname As String
    Dim index As Long
    index = 0
    Do
    Err.Clear
    index = index + 1
    sname = "template" & Format$(index, "000")
    ws.Name = sname
    Loop While Err.Number <> 0
    End Sub

    "foxgguy2005" wrote:

    >
    > I have a Setup sheet, which has a button to creat a new sheet, which
    > actually copies a tamplet. When i click this button it calls the new
    > tab "Tamplet (2)"
    > Is there a sub i can put in the copy routine to auto-rename the tabs
    > sequencially?
    > Note number of tabs is based upon how many times the user clicks the
    > new page button.
    >
    > Thanks!
    > ~Josh
    >
    >
    > --
    > foxgguy2005
    > ------------------------------------------------------------------------
    > foxgguy2005's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=23663
    > View this thread: http://www.excelforum.com/showthread.php?threadid=378885
    >
    >
     
    Guest, Jun 14, 2005
    #2
    1. Advertisements

  3. foxgguy2005

    foxgguy2005 Guest

    I tried putting this into my command button as follows:
    but it does not work, i'm not quite sure what i'm doing wrong here?
    Thanks!

    Code
    -------------------
    Private Sub CommandButton1_Click()
    Dim ws As Worksheet
    Set ws = Worksheets("Tamplet")
    ws.Copy Worksheets(1)
    SetSheetName Worksheets(1)
    End Sub
    Private Sub SetSheetName(ws As Worksheet)
    On Error Resume Next
    Dim sname As String
    Dim index As Long
    index = 0
    Do
    Err.Clear
    index = index + 1
    sname = "Tamplet" & Format$(index, "000")
    ws.Name = sname
    Loop While Err.Number <> 0

    End Su
    -------------------

    --
    foxgguy200
    -----------------------------------------------------------------------
    foxgguy2005's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2366
    View this thread: http://www.excelforum.com/showthread.php?threadid=37888
     
    foxgguy2005, Jun 14, 2005
    #3
  4. foxgguy2005

    foxgguy2005 Guest

    foxgguy2005, Jun 15, 2005
    #4
  5. I put a commmandbutton on a worksheet, double clicked on that commandbutton and
    pasted your code.

    It worked right out of the box.

    If you're using xl97, try changing the commandbutton's .takefocusonclick
    property to false.

    If that's not it, what happened when you tried it?

    foxgguy2005 wrote:
    >
    > I tried putting this into my command button as follows:
    > but it does not work, i'm not quite sure what i'm doing wrong here?
    > Thanks!
    >
    > Code:
    > --------------------
    > Private Sub CommandButton1_Click()
    > Dim ws As Worksheet
    > Set ws = Worksheets("Tamplet")
    > ws.Copy Worksheets(1)
    > SetSheetName Worksheets(1)
    > End Sub
    > Private Sub SetSheetName(ws As Worksheet)
    > On Error Resume Next
    > Dim sname As String
    > Dim index As Long
    > index = 0
    > Do
    > Err.Clear
    > index = index + 1
    > sname = "Tamplet" & Format$(index, "000")
    > ws.Name = sname
    > Loop While Err.Number <> 0
    >
    > End Sub
    > --------------------
    >
    > --
    > foxgguy2005
    > ------------------------------------------------------------------------
    > foxgguy2005's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=23663
    > View this thread: http://www.excelforum.com/showthread.php?threadid=378885


    --

    Dave Peterson
     
    Dave Peterson, Jun 15, 2005
    #5
  6. foxgguy2005

    foxgguy2005 Guest

    foxgguy2005, Jun 17, 2005
    #6
  7. I'd say Patrick was the man!

    foxgguy2005 wrote:
    >
    > yeah you know it works for mee to actually, i'm retarded, lol.
    >
    > Thanks man, tweaked the code for only 2 index numbers and to not
    > include the tamplet... works like a charm, you are the MAN!
    >
    > --
    > foxgguy2005
    > ------------------------------------------------------------------------
    > foxgguy2005's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=23663
    > View this thread: http://www.excelforum.com/showthread.php?threadid=378885


    --

    Dave Peterson
     
    Dave Peterson, Jun 17, 2005
    #7
  8. foxgguy2005

    foxgguy2005 Guest

    foxgguy2005, Jun 17, 2005
    #8
  9. foxgguy2005

    foxgguy2005 Guest

    foxgguy2005, Jun 17, 2005
    #9
  10. Yep.

    Option Explicit
    Public Sub AddSheets()
    Dim ws As Worksheet
    Set ws = Worksheets("template")
    ws.Copy _
    before:=Worksheets("setup")
    SetSheetName ActiveSheet
    End Sub
    Private Sub SetSheetName(ws As Worksheet)
    On Error Resume Next
    Dim sname As String
    Dim index As Long
    index = 0
    Do
    Err.Clear
    index = index + 1
    sname = "template" & Format$(index, "000")
    ws.Name = sname
    Loop While Err.Number <> 0
    End Sub


    When you copy a sheet, it becomes the activesheet. So we just pass the
    activesheet to the "setSheetName" subroutine.



    foxgguy2005 wrote:
    >
    > an another quick note is there a way to copy them before a certain
    > sheet
    > IE: Before:=Sheets("Setup")
    >
    > --
    > foxgguy2005
    > ------------------------------------------------------------------------
    > foxgguy2005's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=23663
    > View this thread: http://www.excelforum.com/showthread.php?threadid=378885


    --

    Dave Peterson
     
    Dave Peterson, Jun 17, 2005
    #10
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. John Smith

    copy Sheet and rename it!

    John Smith, Sep 7, 2004, in forum: Microsoft Excel Programming
    Replies:
    3
    Views:
    209
    Bernie Deitrick
    Sep 7, 2004
  2. Guest

    Copy a sheet and rename it

    Guest, Mar 24, 2005, in forum: Microsoft Excel Programming
    Replies:
    4
    Views:
    138
    Bob Phillips
    Mar 24, 2005
  3. Guest

    Run Macro & Rename Sheet upon creating copy from blank.

    Guest, Oct 14, 2005, in forum: Microsoft Excel Programming
    Replies:
    1
    Views:
    194
  4. Simon Lloyd
    Replies:
    0
    Views:
    283
    Simon Lloyd
    May 12, 2006
  5. Howard
    Replies:
    8
    Views:
    197
    Howard
    Oct 12, 2013
Loading...

Share This Page