Macro help - please! #2

Hi all,
is there a way to copy the name a user inputs (say in cell ref A1) and 
create a new worksheet within my current workbook with this 'name' using a 
macro ??.
ie
the user inputs Bloggs,Joe into cell ref A1, and once the macro is selected 
a new worksheet is created with the 'tab' now changed to Bloggs,Joe - instead 
of Sheet1, Sheet2 etc etc
thanks
PSA
I'm a novice  -so sorry if this is simple
0
Anthony2219 (255)
7/6/2005 10:49:07 PM
excel.misc 78881 articles. 5 followers. Follow

5 Replies
288 Views

Similar Articles

[PageSpeed] 33

One way:

Sub NewSheet()
Dim x As Worksheet
    Set x = ActiveSheet
    Sheets.Add
    ActiveSheet.Name = x.Range("A1")
End Sub

-- 
tj


"Anthony" wrote:

> Hi all,
> is there a way to copy the name a user inputs (say in cell ref A1) and 
> create a new worksheet within my current workbook with this 'name' using a 
> macro ??.
> ie
> the user inputs Bloggs,Joe into cell ref A1, and once the macro is selected 
> a new worksheet is created with the 'tab' now changed to Bloggs,Joe - instead 
> of Sheet1, Sheet2 etc etc
> thanks
> PSA
> I'm a novice  -so sorry if this is simple
0
tjtjjtjt (488)
7/6/2005 11:06:02 PM
Try something similar to:

    MyName = Range("A1").Value
    Sheets.Add
    ActiveSheet.Name = MyName

-- 
Damon Longworth

Don't miss out on the 2005 Excel User Conference
Sept 16th and 17th
Stockyards Hotel - Ft. Worth, Texas
www.ExcelUserConference.com


"Anthony" <Anthony@discussions.microsoft.com> wrote in message 
news:60E5C295-CCC2-4A98-87ED-FD59BE5AC329@microsoft.com...
> Hi all,
> is there a way to copy the name a user inputs (say in cell ref A1) and
> create a new worksheet within my current workbook with this 'name' using a
> macro ??.
> ie
> the user inputs Bloggs,Joe into cell ref A1, and once the macro is 
> selected
> a new worksheet is created with the 'tab' now changed to Bloggs,Joe - 
> instead
> of Sheet1, Sheet2 etc etc
> thanks
> PSA
> I'm a novice  -so sorry if this is simple 


0
dlongwth (59)
7/6/2005 11:07:34 PM
thanks for help - I'll give it  go,
one other thing, I assume if I want also to paste some text into this newly 
created worksheet from the first sheet this can also be done,
ie the new worksheet is created and renamed Bloggs,Joe (as this is entered 
into cell A1), and cells B2,C3,D4 and copied from this sheet and pasted into 
cells X1,Y2,Z3 of the new worksheet.
thanks again

"tjtjjtjt" wrote:

> One way:
> 
> Sub NewSheet()
> Dim x As Worksheet
>     Set x = ActiveSheet
>     Sheets.Add
>     ActiveSheet.Name = x.Range("A1")
> End Sub
> 
> -- 
> tj
> 
> 
> "Anthony" wrote:
> 
> > Hi all,
> > is there a way to copy the name a user inputs (say in cell ref A1) and 
> > create a new worksheet within my current workbook with this 'name' using a 
> > macro ??.
> > ie
> > the user inputs Bloggs,Joe into cell ref A1, and once the macro is selected 
> > a new worksheet is created with the 'tab' now changed to Bloggs,Joe - instead 
> > of Sheet1, Sheet2 etc etc
> > thanks
> > PSA
> > I'm a novice  -so sorry if this is simple
0
Anthony2219 (255)
7/6/2005 11:25:03 PM
Do you have a larger plan in mind? This does what you are asking, but with 
more information, someone could probably come up with something better.

Sub NewSheet()
Dim x As Worksheet
Dim y As Worksheet
Dim z As Variant
       
    Set x = ActiveSheet
    Sheets.Add
    ActiveSheet.Name = x.Range("A1")
    Set y = ActiveSheet
    
    z = Array(x.Range("B2"), x.Range("C3"), x.Range("D4"))
    y.Range("X1") = z(0)
    y.Range("Y2") = z(1)
    y.Range("Z3") = z(2)
    
End Sub

-- 
tj


"Anthony" wrote:

> thanks for help - I'll give it  go,
> one other thing, I assume if I want also to paste some text into this newly 
> created worksheet from the first sheet this can also be done,
> ie the new worksheet is created and renamed Bloggs,Joe (as this is entered 
> into cell A1), and cells B2,C3,D4 and copied from this sheet and pasted into 
> cells X1,Y2,Z3 of the new worksheet.
> thanks again
> 
> "tjtjjtjt" wrote:
> 
> > One way:
> > 
> > Sub NewSheet()
> > Dim x As Worksheet
> >     Set x = ActiveSheet
> >     Sheets.Add
> >     ActiveSheet.Name = x.Range("A1")
> > End Sub
> > 
> > -- 
> > tj
> > 
> > 
> > "Anthony" wrote:
> > 
> > > Hi all,
> > > is there a way to copy the name a user inputs (say in cell ref A1) and 
> > > create a new worksheet within my current workbook with this 'name' using a 
> > > macro ??.
> > > ie
> > > the user inputs Bloggs,Joe into cell ref A1, and once the macro is selected 
> > > a new worksheet is created with the 'tab' now changed to Bloggs,Joe - instead 
> > > of Sheet1, Sheet2 etc etc
> > > thanks
> > > PSA
> > > I'm a novice  -so sorry if this is simple
0
tjtjjtjt (488)
7/6/2005 11:59:01 PM
Hi,
well basically I have several bits of data which has been input by the user 
on a 'order form' and I want a macro to create a new worksheet for each new 
order placed calling it the name given in cell ref A1. Also I want all the 
other data copy/pasted into this new worksheet from the original 'order form'
hope this is clear and thanks for help this far

"tjtjjtjt" wrote:

> Do you have a larger plan in mind? This does what you are asking, but with 
> more information, someone could probably come up with something better.
> 
> Sub NewSheet()
> Dim x As Worksheet
> Dim y As Worksheet
> Dim z As Variant
>        
>     Set x = ActiveSheet
>     Sheets.Add
>     ActiveSheet.Name = x.Range("A1")
>     Set y = ActiveSheet
>     
>     z = Array(x.Range("B2"), x.Range("C3"), x.Range("D4"))
>     y.Range("X1") = z(0)
>     y.Range("Y2") = z(1)
>     y.Range("Z3") = z(2)
>     
> End Sub
> 
> -- 
> tj
> 
> 
> "Anthony" wrote:
> 
> > thanks for help - I'll give it  go,
> > one other thing, I assume if I want also to paste some text into this newly 
> > created worksheet from the first sheet this can also be done,
> > ie the new worksheet is created and renamed Bloggs,Joe (as this is entered 
> > into cell A1), and cells B2,C3,D4 and copied from this sheet and pasted into 
> > cells X1,Y2,Z3 of the new worksheet.
> > thanks again
> > 
> > "tjtjjtjt" wrote:
> > 
> > > One way:
> > > 
> > > Sub NewSheet()
> > > Dim x As Worksheet
> > >     Set x = ActiveSheet
> > >     Sheets.Add
> > >     ActiveSheet.Name = x.Range("A1")
> > > End Sub
> > > 
> > > -- 
> > > tj
> > > 
> > > 
> > > "Anthony" wrote:
> > > 
> > > > Hi all,
> > > > is there a way to copy the name a user inputs (say in cell ref A1) and 
> > > > create a new worksheet within my current workbook with this 'name' using a 
> > > > macro ??.
> > > > ie
> > > > the user inputs Bloggs,Joe into cell ref A1, and once the macro is selected 
> > > > a new worksheet is created with the 'tab' now changed to Bloggs,Joe - instead 
> > > > of Sheet1, Sheet2 etc etc
> > > > thanks
> > > > PSA
> > > > I'm a novice  -so sorry if this is simple
0
Anthony2219 (255)
7/7/2005 12:07:03 AM
Reply:

Similar Artilces:

List Boxes #2
Can someone tell me how to correct the following code so that both columns in the listbox will be updated? Only the first column distplays Dim MyArray() ReDim MyArray(mycount, 1) usrGLDist.lstOutput.ColumnCount = 2 For i = 0 To rst.Count - 1 MyArray(i, 0) = rst.Value("descr") MyArray(i, 1) = rst.Value("pcamt") rst.MoveNext Next i 'Load ListBox1 usrGLDist.lstOutput.List() = MyArray Richard wrote : > Can someone tell me how to correct the following code so that both > columns in the listbox will be updated? Only the first col...

Outlook macro question
I have two email accounts in Outlook 2003. I know about the Accounts drop list in new emails. But is it possible to create a few macros so that I end up with two buttons where each runs a macro that starts a new email using a specific account? One button would start a new email using account 1 and the other button would start a new email using account 2. If so, could someone please give me some pointers? Thanks. Outlook doesn't provide any direct way to change the account for an = outgoing message in versions before Outlook 2003. See=20 http://www.outlookcode.com/codedetail.aspx?id=3D88...

Making a macro "auto open"
Hello, I have the following macro, which lets me set the zoom level for a document to 75%: Sub View75percent() ' ' View75percent Macro ' ' ActiveWindow.ActivePane.View.Zoom.Percentage = 75 End Sub My question is: how can I edit this to make it an "autoopen" macro, so that it will automatically open all existing documents to this zoom level? Many thanks, Josh Mandel You just call it AutoOpen. You can only have one of these that applies. Same with AutoExec and AutoNew. JoshMandel wrote: >Hello, > >I h...

SP 1 & 2 Updates
I noticed that when I installed the SP 1 & 2 Updates, I could no longer get .zip, .doc, .htm, .exe attachments and anything like that (.jpg and .gif are no problem). Is there a way to enable attachments of other types to come through? I would like to install the Updates, but need to be able to receive attachments. Thanks for your help. ...

xp style #2
How can i add the xp style to my programs like bitmap buttons and tool bar and menu http://www.codeproject.com/cpp/xpstylemfc.asp Tom "mido1971" <mido1971@discussions.microsoft.com> wrote in message news:636A0B44-1B8B-4E31-AF7B-86A4B9486AB0@microsoft.com... > How can i add the xp style to my programs like bitmap buttons and tool bar > and menu thanks its work but how can i use it in bitmapbuttons "mido1971" wrote: > How can i add the xp style to my programs like bitmap buttons and tool bar > and menu "mido1971" <mido1971@discussi...

adding and counting Help!! #2
Just to add, I'd like to sort the dates 2004.10.01 to 2004.10.31 whe doing the sum of dollar amount -- alexm99 ----------------------------------------------------------------------- alexm999's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=491 View this thread: http://www.excelforum.com/showthread.php?threadid=27264 alexm999 Wrote: > Just to add, I'd like to sort the dates 2004.10.01 to 2004.10.31 whe > doing the sum of dollar amount. Assuming that dates are true dates... =SUMPRODUCT(--(CodeRange=101),--(AdvertisingRange="TV"),--(TEX...

Outlook 2003 Add-on help
Is there a way, either built-in to Outlook 2003 or through third party software that will display contacts in a alphabetical list when composing a mail message? Specifically, the user wants to be able to select a letter, e.g. "A", "B", "C", etc... when composing a message (when clicking the "To" field in the mail form) that will pull up an alphabetical listing of contacts from the address book. I know this capability resides in OWA, but I have yet to find a solution for Outlook 2003. Any assistance would be greatly appreciated. Thanks, Ryan ryan.hopm...

Help: my reminders don't work
Help: I'm running OL 2003 on WinXP pro connected to an exchange server. I run this configuration on both my laptop and my desktop. On my desktop the calendar reminder pop ups and alert sounds don't work but they do on my laptop. As far as I can tell, the configuration is identical. I've tried the /cleanreminders and /resetfolder switches, but the don't make any difference. Any advice appreciated. Thanks. -- Dab Cut off: yourhead to respond Have you checked your Sounds and Multimedia options on the desktop to ensure that your sounds are working correctly and th...

Exchange Server 5.5 SMTP log #2
I am running Exchange server 5.5 on a LAN. How can I track email by user. I would like to see what email a user receives and sends on a daily basis (don't want to read the mail, but the header and stuff like that). Can I do this with Exchange (so yes how) or is their a 3rd party software package I can purchase? Message Tracking logs will give you sender, recipient, size and date of the message. Message Journaling will give you message contents. "Michael J." wrote: > I am running Exchange server 5.5 on a LAN. How can I track > email by user. I would like to see what...

Multi CSV file import #2
I have about 200 csv files that I would like to import into excel. After figuring out that I can only import 1 file at a time, I am tryin to seek a solution to this time consuming problem. Is there an easy wa to import them all at one time, (all the same layout). Is there a way t merge them all and import them, or any other solution? Jef -- Message posted from http://www.ExcelForum.com ...

In Publisher "Help" there is nothing about inserting FOOTNOTES or.
How do you insert footnotes or endnotes in Publisher? Hi pastorcam (pastorcam@discussions.microsoft.com), in the newsgroups you posted: || How do you insert footnotes or endnotes in Publisher? You need to create them manually. Publisher does not offer footnote/endnote features. -- Brian Kvalheim Microsoft Publisher MVP http://www.publishermvps.com This posting is provided "AS IS" with no warranties, and confers no rights. ...

Crazy warning appearing everyday every 2 seconds hundred times
I have an exchange server that 2 mailbox stores and a public store, exchange 2003 with SP2 and i see a very annoying warning everyday on the event logs. Event Type: Warning Event Source: MSExchangeTransport Event Category: Exchange Store Driver Event ID: 327 Date: 3/16/2006 Time: 11:16:47 AM User: N/A Computer: (MailServer) Description: The following call : EcLocallyDeliverMsg to the store failed. Error code : -2147221240 (Message-ID <7DFCADDF870056499AE0B3AB82058C3701177854@svrmail.xdomain.com>). MDB : 5f2a6494-f8c9-42eb-8db9-fbd1e92a806e. FID : . MID : . File : D:\Exchsrvr\Qu...

Report filter error #2
I get the following error when running a prefilter report in CRM 3.0 but in VS in runs fine Reporting Services Error -------------------------------------------------------------------------------- a.. An error has occurred during report processing. (rsProcessingAborted) Get Online Help a.. Cannot set the command text for data set 'Kunde'. (rsErrorSettingCommandText) Get Online Help a.. Error during processing of the CommandText expression of dataset 'Kunde'. (rsQueryCommandTextProcessingError) Get Online Help Here is the sql statement SELECT accountnumber F...

Loosing Data-Help needed
Hi All, I have an huge spreadsheet with macros on it, which is continuously updated. Off recently my spreadsheet is loosing the data i.e. I will have 2-3 days old information, all the updates that we made recently is lost. I am saving the spreadsheet every 15-20 mins still it is doing it. Could anyone please help me :confused: . Thank You, Naveen -- neomemphis ------------------------------------------------------------------------ neomemphis's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=33240 View this thread: http://www.excelforum.com/showthread.php?th...

Simple question #2
For some reason, when people reply to anything I FORWARDED, the original sender of the email (the person that sent it to me) is getting the reply. Why? Thanks. Hi Amon, When you forward an email it retains all of its original characteristics INCLUDING the originators email address. Accordingly, if the person you sent the mail to then simply clicks the "Reply" button in Outlook then their message will go to the originator and NOT back to you. If they want to reply to you they will need to insert your email address. In effect, Outlook is working the way it was intended ...

Compare 2 lists
Is there a native way in Excel to compare List1 and List2 and find out which records are missing between them? Up to now I have imported the information into Access and done an unmatched query. Windows 2000 OS MS Office 2000 Dave French Hi Dave, See Chip Pearson's page http://www.cpearson.com/excel/duplicat.htm#ExtractingCommon Extracting Values On One List And Not Another (just below the above link) --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/d...

RUS #2
RUS is not setting emails for child domain. Anyone know what permissions need to be set on the child domain for it to update the users? I have run dominprep and my other child domain works fine. Thank you in advance. "Chris Macera" <kirmit99@yahoo.com> wrote: >RUS is not setting emails for child domain. Anyone know >what permissions need to be set on the child domain for >it to update the users? I have run dominprep and my >other child domain works fine. >Thank you in advance. have you actually got a Recipient Update Service entry for the child doma...

Moving databases #2
Hello, I know already know that my question is not a recommended solution but due to current circumstances it might be my only option. Is there anything that would stop me from moving the exchange databases to an external hard drive? I know I will most likely suffer a major decrease in performance among the other issue. But i need to know if there is anything that would prevent me from moving the database to the external hard drive. thx. Why don't you first try telling us what issues you are having and maybe someone here can give you a better solution. -- TIA, Clayton P.S...

** One Million FR.EE Visitors ** #2
How To Get 1 Million Visitors On Your Web Site Without Paying A Dime In advertising ! Are you frustrated by the lack of traffic coming to your site? If I would tell you that after months of research, I just got my hands on the most hidden secrets... very sneaky tricks ! Click here : http://www.freeadguru.com/cgi-bin/i.pl?c=a&i=30129 Hurry, before the page gets banned! Regards Bruce SMART --- MAF Anti-Spam ID: 20050916185548U6g4SxD2 ...

Help! #12
It repeatedly asks for my network password.And doesnt recognise it when i enter it. I havent changed the password from the original. Has this happened to anyone else? Does anyone know how to fix it? Do you mean that Outlook keeps asking for your Outlook password? Check CapLocks, etc. I believe Outlook passwords are case specific. "Leslie" <anonymous@discussions.microsoft.com> wrote in message news:03c601c496bc$95bd84a0$a401280a@phx.gbl... > It repeatedly asks for my network password.And doesnt > recognise it when i enter it. I havent changed the > password from ...

Column Sum in Footer
Hi, I have created a Tabular report that lists names and weights for different people. I want to create a stand-alone field in the page footer that will display the SUM of all the weights on the report. Can somebody please help me? (I'm using Access 2000 btw) I have tried creating a text box with the Control Source property set as "=Sum(weight)". However, this just produces an error. Thanks in advance. Julie Smith wrote: >Hi, >I have created a Tabular report that lists names and weights for different >people. I want to create a stand-alone field in the page f...

How can I open files from a prev. version of Publisher in Pub 2000 #2
Is there a way to open files from an earlier version of Publisher in Publisher 2000? I have a file that I cannot access, that I believe was created in Publisher 97 or 98, that I need to access, but I get an error message that says it cannot be opened. You should be able to open it. Maybe it is a 2002 or 03 document. Do you have Norton? Disable script blocking and in Norton Options Office Plug ins. If that doesn't work send the files to me and I will convert them for you. Remove "my" to reply. -- Mary Sauer MS MVP http://office.microsoft.com/ http://msauer.mvps.org/ news:...

Macro to add name on a cell based on the data
Hello, I am using Excel 2003 and I need help to do a macro. I copy and paste data into a worksheet call "Table" In the worksheet I want to put a specialist name in the column called "Specialist" beside a column called "STATE" the specialist name will be based on what the state is. For example If the state contains "CA" I want the specialist name to be "Anna" or the state of "OR" I again want the specialist name to be "Anna" Now if we get a different state "UT" I want the specialist name to be "...

FORECASTING SALES (please let this be a worksheet function)
Hi I have recently been given the task by my boss to forecast one of ou key customers monthly usage until monthly until end of 2006, I am starting with this customers indidual branches monthly usage fo the past 3 years, Some branches will have opened and closed during thi time, And i am looking to be able to predict monthly usage for the nex 18 months Has anyone got any idea's on the best way to forcast within excel, I a currently using trend (fomulae given to me on this sight) but people ar saying this is not the best way Exponentionally has been mentioned by a few of my collegues but...

opening file problem please help
When I click on an Excel file in Windows Explorer, I get the following error message. "a document with the name "filename".xls is already open. You can not open two documents with the same name at the same time". (Clicking ok closes the message and the file opens.) This happens on every Excel file in Explorer. There are no other files open on my p.c. at all. If I open Excel first and open the file I want by clicking the File menu and selecting Open, the file will open with no error message. Other than that the programme works perfectly I'm using Excel 97 and Windo...