Creating a copy of a sheet and naming it

Being a newbie to VBA could I please have some advice .

What I am trying to do is write a macro to select the active sheet and
create a copy of it but then I would like an input message to ask me
what to name the sheet so that I am presented with an exact copy
(including macro buttons) but with a name of my choice.  I have tried a
myriad of combinations but do not seem able to get it to work

This macro must be able to work with any selected sheet in the workbook

Any help would be appreciated

Brian
Scotland

0
11/3/2006 7:39:22 PM
excel 39879 articles. 2 followers. Follow

2 Replies
534 Views

Similar Articles

[PageSpeed] 31

Brian,

SheetName = InputBox("Enter desired name for copy of active sheet")
activesheet.Copy After:=ActiveSheet
ActiveSheet.Name = SheetName

It will copy the selected sheet and put it sheet after the selected sheet. 
It won't check that the name entered already exists in a sheet, which will 
cause an error.

As a starter, you can start the macro recorder (Tools - Macro - Record new 
macro), do the biz, stop the recorder, then modify the code as needed.

-- 
Earl Kiosterud
www.smokeylake.com
-----------------------------------------------------------------------
<brian.jermain@googlemail.com> wrote in message 
news:1162582762.775745.199660@b28g2000cwb.googlegroups.com...
> Being a newbie to VBA could I please have some advice .
>
> What I am trying to do is write a macro to select the active sheet and
> create a copy of it but then I would like an input message to ask me
> what to name the sheet so that I am presented with an exact copy
> (including macro buttons) but with a name of my choice.  I have tried a
> myriad of combinations but do not seem able to get it to work
>
> This macro must be able to work with any selected sheet in the workbook
>
> Any help would be appreciated
>
> Brian
> Scotland
> 


0
someone798 (944)
11/3/2006 8:27:00 PM
Earl

Thank you very much - you make it look so easy

Much appreciated

Brian

0
11/3/2006 8:45:20 PM
Reply:

Similar Artilces:

calculating the name of a worksheet
is there a way to create a formula that changes the name of a sheet within another formula. In other words, the following is a reference to an external workbook within an IF Function -- '[SDS.xls]we121'!$L$3. The sheet name we121 will change on a wkly basis by 7 days. I would like to have the formula automatically look at the worksheet that is we121+7 OR we128. Is it possible? Thank you. One way to consider Earmark a cell say, A1 to contain the numbers: 121, 128, etc Then we could put in say, A2: =INDIRECT("[SDS.xls]we"&A1&"!$L$3") If A1 contains: 1...

create a floating bar graph with min and max (price for example)
How to I create a graph with floating bars showing price on the y axis so that I can set a custom min/max for each category. For example I want to compare the min/max price range for a variety of things - each is going to have a different min and max, but all are in the same basic range. I can add a line through the middle that shows average for all things within the category. There is a floating bar graph in my version of PPT, but they are horizontal and I want them to be vertical. - How? EXCEL 2007 I have just put up a file for you at:- http://www.pierrefondes.c...

FQDN vs Domain DNS name error
A user got the following error msg when she tried to send mail to a Comcast address recently: There was a SMTP communication problem with the recipient's email server. Please contact your system administrator. <netbiosname.subdomain.domain.com #5.5.0 smtp;521-EHLO/HELO from sender 12.34.56.78 does not map to netbiosname.subdomain.domain.com in DNS> In ESM->...Protocols->SMTP->Default SMTP virtual server properties->Delivery->Advanced the Fully-qualified domain name was set to the NetBIOS name of the server vs the public DNS name. I changed it to...

user name history
Not sure what you call this. When I open a website and I have to type in the user name, after the first letter it predicts the user name. However I have typed many errors so when this happens I have about 7 names appear, is there a way to delete the error ones? thanks SS wrote: > Not sure what you call this. > > When I open a website and I have to type in the user name, after the first > letter it predicts the user name. > However I have typed many errors so when this happens I have about 7 names > appear, is there a way to delete the error ones? ...

copy a formula that always references column AV6-AV35
I have a subtotal on another sheet and would like several columns i another sheet to ref cell AV6 and drag that formula down but when copy/paste the formula over to the next column it puts AW6 in and want to always start with AV6 in every column so I can drag the formul down to multiply AV7, AV8,...in each column. Thanks -- Message posted from http://www.ExcelForum.com before copying the the adjacent column change the reference to $AV7 thi will lock it to AV but allow you to copy down to get you autofil values. Similarly AV$7 would lock the row reference and $AV$7 would lock both. A qui...

What Is The Name This Chart Type
Greeting, I would like to build a chart for scheduling workers. What I would like is a chart with time across the top and workers down the left side. There are a number of jobs to be done each day. These jobs range from 1 to 20 hours, most are 2 to 4 hours. The time is broken down by 12 minute intervals. I need to assign each worker as many jobs as they can handle. I saw a chart that was set up with time across the and workers down the side and the jobs are blocked off in the time the job took, in the row of the worker. Each worker is blocking off from 1 to 4 jobs a day. Somethin...

Copy Rows if Lookup Criteria Match
I am trying to create sometype of template to pull data associated with a specific office. I have a drop down list create on WorksheetB. I have another WorksheetA that contains a list of equipment (Office, Equipment, Lease End Date, Cost, etc..). I would like to be able to select the Office from the drop down list on WorksheetB and pull all of the rows that are associated with the Office in WorksheetA back over to Worksheet B. I know that I can do this using Access Queries, but is there a way to do this in Excel? If so, what are your recommendations? ...

creating a Newsreader shortcut
I am using Outlook 2000 for my Newsreader and was wondering if there is anyway to create a desktop icon that would open just the Newsreader? Right now, I must open either Outlook or IE then click on the menu to launch the newsreader, I don't want to have to do that anymore, an icon would be nice. Thanks. -- marx404 outlook actually uses outlook express for news. Copy an OE shortcut onto the desktop, and in the Target field under properties add the switch "/outnews" (without the quotes). "mjh404" <nospam@nospam.net> wrote in message news:GURrb.1389$9l4.887992...

Mailbox not create !
This morning, my First storage group was down! Now, it is running but my recipient policy (Smtp & X.400) is not applied. So, when I create a new user account, he can join the domain but with no mail box. Do you have any advice to give me. Merci for your collaboration. Benedicte Ok! I found the solution. I did an update now on the RUS and restart the mail server. Merci tout de même. Benedicte ...

@how to select Name Box from ...
Sir, How to select Name box and Formula Bar from keyboard? And how to rename a worksheet tab from keyboard? thanks in advance. San The F2 key will cause Excel to enter Edit mode. If the "Edit In Cell" option is enabled, focus will be in the edit mode of the cell. If "Edit In Cell" is not enabled, focus goes to the formula bar. For your other questions, you can only address them with code. To set focus in the Name dialog, paste the following in a code module: Public Declare Function SetFocus Lib "user32" (ByVal hwnd As Long) As Long Pu...

Running ISE to create a new file
When I try to run ISE to create a new file from the PS prompt: > ise newfile.ps1 I get an error that the file does not exist, which then proceeds into editing an untitled ps1 file. What I'd like it to do is create the new file if it does not exist. I'm sure that there's a simple way to do this, but I can't seem to find it. I suppose I could create a script or function to do it, but I would think that ISE could do this on its own. Suggestions would be appreciated. > Suggestions would be appreciated. Something like this maybe below... Now, oddly, I...

Create a new 2005 data file with 2004 settings.
Hello: I am using Money 2004. I would like to create a new data file for 2005 but retain my settings from the 2004 data file I currently have. Is there a way to do this? Basically I need to use the 2004 data file settings as a template for 2005. I want to retain my accounts, and various lists... Thanks Harold In microsoft.public.money, harold wrote: > >I am using Money 2004. I would like to create a new data file for 2005 but >retain my settings from the 2004 data file I currently have. Is there a way >to do this? Basically I need to use the 2004 data file settings as a ...

how do I remove my name from the top of the page when printing
I don't want my name to print on the top of the page when I print out my contacts. I want to pass out the contact information to my business agents but my name appears at teh top of every page. How can I get rid of my name "Engineers98" <Engineers98@discussions.microsoft.com> wrote in message news:BF9AFF8D-222E-4903-B430-C16BCED3ED44@microsoft.com... > I don't want my name to print on the top of the page when I print out my > contacts. I want to pass out the contact information to my business > agents > but my name appears at teh top of eve...

Copying data
Hi How do I set up a drop down list (Sales April, May, June ect) to copy data from 1 column to another in a specified location? Thanks Andre You can use and Advanced Filter to copy data from one location to another. There are instructions in Excel's Help, and here: http://www.contextures.com/xladvfilter01.html Also, there are sample files here that use Data Validation to create dropdown lists, and Advanced Filter to extract data: http://www.contextures.com/excelfiles.html Under Filters, look for 'Product List by Category ' Andre wrote: > How do I set u...

Copy Business Alerts to other companies
Allow the copying of Business Alerts to other companies so you don't have to set the same Alert from scratch for each company. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businesssolutions/Community/NewsGroups/dgbrowser/en-us/default.ms...

Disable Copying
Is there a way to disable users from copying a protected template to a new file? Not really. Anything that I would use would be macro based. And macros can be disabled by the user. And heck, they can just use windows explorer to copy and paste whereever they like. Chris wrote: > > Is there a way to disable users from copying a protected > template to a new file? -- Dave Peterson ec35720@msn.com ...

Excel copy + paste
When I open up a new Excel workbook and try to paste cells from another workbook it is pasting the cells as a ( bmp ) It actually pastes the cells as a picture and the picture tool opens. I can paste cells fine within the same workbook but once I open another workbook and try to paste into it this happens. Help!! You are opening the second workbook in its own instance of Excel so you have two instances running. They do not see each other so you get the behaviour you describe. Close one instance and workbook then open workbook again in the other single instance. To p...

Cannot create users 12-26-03
I just recently installed SBS2003. I have had CRM since the end of novemeber and was going to install it on this new server once it was built. Everything installed fine until I went to create a user. I am logged on as administrator and I try to access the site to create user. I get am error trying to open the site in order to add a license to the user. I cant even go trough the deployment manager to add a user because it tells me I do not have sufficient privileges. Has anybody run into this problem and had found a work around? Just for technical purposes I am running SBS2003 on a...

how to resize the dynamic combobox created inside a listctrl........????????????
how to resize the dynamic combobox created inside a listctrl........???????????? > how to resize the dynamic combobox created inside a > listctrl........???????????? Regardless of where it is created, you can use SetWindowPos to size it. --- Ajay ...

I get an error when sending sheet via email
I am running office 2000. I recently updated Outlook to Outlook2003. Now, none of the other applications can email. When I click the icon I get an error that the email application can not be found. I was able to send an excel sheet as an attachment, but I need to be able to send as the message body. Any help would be great. Thanks Marty Hi Marty See number 3 http://www.rondebruin.nl/mail/problems.htm A workaround is to use my Add-in (Outlook version) http://www.rondebruin.nl/mail/add-in.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Marty" <Martyr@peerohio.com...

Copy MP3 From Player to New System
Hello. I want to do a reverse sync from my mp3 player into Windows Media Player. I have a new PC and I want to move my music into it from my mp3 player. What are the steps? After I typed this, I did a search on "reverse sync". Yes. If you have a device that supports copying from the device to the Player Library (sometimes called "reverse sync"), you can copy a file from your device to your computer. If the device recognizes the files as new, and you have an automatic sync relationship, then the files will be automatically copied to your computer. To ...

VLOOKUP
When I query a datatable using VLOOKUP I get the answer returned fro the first instance of a name that appears albeit in the correct column If ABC is in row 2 with a value of 5 and also in row 3 with a value o 6, I get the reult 5 from row 2 in my formula. What I need is for formula that will sum all instances of the row name ABC so giving m the answer 11 (5+6). Ideally I don't want to go into VB scripting bu can handle nesting ormulae. I don't want to use dat>subtotals either a I need it to work 'behind the scenes' I am using Excel 2000. Any help would be gratefully receiv...

Create a Mailbox
I create a new User in Active Directory then created a mailbox. Name is Verias properties have the Exchange Tabs but from System Manager under mailbox I don't see my newly created user. Why? Thanks for your help! it will not appear in "mailbox resources" until it's been logged into or it receives a piece of email... "Maida" <anonymous@discussions.microsoft.com> wrote in message news:184301c49c1c$54a28190$a401280a@phx.gbl... > I create a new User in Active Directory then created a > mailbox. Name is Verias properties have the Exchange Tabs > but ...

copy formulas from previous line
A simple quesrtion, I hope: how can I make excel copy the formulas from the previous row when I start a new line? What I have now is that I have to copy the formula by hand. When I take the grip and pull it down the formula is copied, but in all the cells of the column appears already a value. That's not a nice sight. Can you help me? Regards from Belgium. Wilfried Hi what formulas are you using currently?. Normally you can use formulas like =IF(A1="","",your_formula) to show a result only if cell A1 is filled -- Regards Frank Kabel Frankfurt, Germany "Wilfr...

Create and Modify a CHM file
I am working on a project. I have to convert some files ( perhaps HTML,MHTML,Doc,Txt etc.) into a single CHM,and also, the User can reload the CHM that had been generated for some modification. I don't even konw how to make and modify a CHM file using MFC. I am using Visual C++ 6.0. I hope some one could give a example. It could just cover only HTML2CHM and reload HTML from CHM for simple Thank you very much! ...