PC Review


Reply
Thread Tools Rate Thread

Button to copy sheet, rename sheet sequencially.

 
 
foxgguy2005
Guest
Posts: n/a
 
      14th Jun 2005

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...o&userid=23663
View this thread: http://www.excelforum.com/showthread...hreadid=378885

 
Reply With Quote
 
 
 
 
=?Utf-8?B?UGF0cmljayBNb2xsb3k=?=
Guest
Posts: n/a
 
      14th Jun 2005
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...o&userid=23663
> View this thread: http://www.excelforum.com/showthread...hreadid=378885
>
>

 
Reply With Quote
 
 
 
 
foxgguy2005
Guest
Posts: n/a
 
      14th Jun 2005

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...fo&userid=2366
View this thread: http://www.excelforum.com/showthread.php?threadid=37888

 
Reply With Quote
 
foxgguy2005
Guest
Posts: n/a
 
      15th Jun 2005

anyone help me out on this one, much appreaciate it

--
foxgguy200
-----------------------------------------------------------------------
foxgguy2005's Profile: http://www.excelforum.com/member.php...fo&userid=2366
View this thread: http://www.excelforum.com/showthread.php?threadid=37888

 
Reply With Quote
 
Dave Peterson
Guest
Posts: n/a
 
      15th Jun 2005
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...o&userid=23663
> View this thread: http://www.excelforum.com/showthread...hreadid=378885


--

Dave Peterson
 
Reply With Quote
 
foxgguy2005
Guest
Posts: n/a
 
      17th Jun 2005

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 no
include the tamplet... works like a charm, you are the MAN

--
foxgguy200
-----------------------------------------------------------------------
foxgguy2005's Profile: http://www.excelforum.com/member.php...fo&userid=2366
View this thread: http://www.excelforum.com/showthread.php?threadid=37888

 
Reply With Quote
 
Dave Peterson
Guest
Posts: n/a
 
      17th Jun 2005
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...o&userid=23663
> View this thread: http://www.excelforum.com/showthread...hreadid=378885


--

Dave Peterson
 
Reply With Quote
 
foxgguy2005
Guest
Posts: n/a
 
      17th Jun 2005

heh yeah, actually thats what i meant! :-P
Thanks big pat!
you too dave, for making me check myself

--
foxgguy200
-----------------------------------------------------------------------
foxgguy2005's Profile: http://www.excelforum.com/member.php...fo&userid=2366
View this thread: http://www.excelforum.com/showthread.php?threadid=37888

 
Reply With Quote
 
foxgguy2005
Guest
Posts: n/a
 
      17th Jun 2005

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...o&userid=23663
View this thread: http://www.excelforum.com/showthread...hreadid=378885

 
Reply With Quote
 
Dave Peterson
Guest
Posts: n/a
 
      17th Jun 2005
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...o&userid=23663
> View this thread: http://www.excelforum.com/showthread...hreadid=378885


--

Dave Peterson
 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
how do i rename a sheet if the rename tab is inactive? Nelson Microsoft Excel Worksheet Functions 1 3rd Mar 2010 11:28 AM
"='sheet 1'!D4" auto fill sheet to sheet ='sheet 2'!D4 mistewalker Microsoft Excel Worksheet Functions 5 7th Jan 2008 12:36 AM
How to copy a sheet and rename it with the value of two cells from the source sheet? Simon Lloyd Microsoft Excel Programming 0 12th May 2006 01:31 AM
relative sheet references ala sheet(-1)!B11 so I can copy a sheet. =?Utf-8?B?Um9uTWM1?= Microsoft Excel Misc 9 3rd Feb 2005 01:51 AM
Inserting a row in sheet A should Insert a row in sheet B, removing a row in Sheet A should remove the corresponding row in sheet B Hannes Heckner Microsoft Excel Programming 1 5th Mar 2004 10:10 AM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 06:46 PM.