Adding a new worksheet

boydr · Apr 18th, 2002, 10:50 PM

I have a worksheet with multiple rows and columns of data. I want to place a command button in cell Z1 (and Z2, Z3 and so on) that will insert a new worksheet (and name it unique based on information in cell A1), and then have it populate various cells on the new worksheet with information in cells A1-Y1. Can this be done?
Can you help please?

robfo0 · Apr 19th, 2002, 02:48 AM

Yes, you can do this. What would but buttons in cells z1, z2 etc be for? i was a little confused by that part.
also, you said populate them to a new worksheet, is this what you mean, you want them in a new worksheet in the same workbook, or did you mean a new workBOOK?
Here is a bit of code assuming you did mean worksheet.

Sub CopyStuff()
Dim CurrSheet As String
Dim NewSheet As String

CurrSheet = ActiveSheet.Name

Sheets.Add
ActiveSheet.Name = Sheets(CurrSheet).Range("A1").Value
NewSheet = ActiveSheet.Name
'this copies range a1 from the first sheet to a1 in the new one. Repeat as needed
Sheets(CurrSheet).Range("A1").Copy Destination:=Sheets(NewSheet).Range("A1")

End Sub

im not sure how familiar you are with excel, but heres how you get this into your workbook:

-Press Alt+F11 in excel to active the VBA screen.
-on the menu, go to Insert->Module
-double click the new created module at the left (probably called module1)
-paste this code
-press Alt+Q to exit VBA

now you can run the code by going to Tools->Macros->Macros...
then select the "CopyStuff" macro to run it, OR you can create a button, then link it to CopyStuff when the link screen comes up

hope that helps

boydr · Apr 19th, 2002, 05:50 PM

I think that might work. I'll try it and get back to you. Thanks for your help. BTW. I did want a new worksheet in the same workbook. I wanted a command button that would invoke the macro on demand on an as needed basis. For example, If I determine that the data in A1 thru Y1 is the data I want, then I want a command button in Z1 that I can click on and have it fill out a new worksheet with the data contained in cells A1 thru Y1.

[ This Message was edited by: boydr on 2002-04-19 16:53 ]

boydr · Apr 20th, 2002, 07:18 AM

Robfo0,
Thank you. The macro script works great with a little modification. I do have one problem. Some of the cells I'm copying have formula's in them, and when I copy them, it only copies the formula. Can I force it to take only the value and not the formula?

[ This Message was edited by: boydr on 2002-04-20 06:27 ]

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 18th, 2002, 10:50 PM	#1
boydr Board Regular Join Date: Apr 2002 Location: Arkansas Posts: 157	I have a worksheet with multiple rows and columns of data. I want to place a command button in cell Z1 (and Z2, Z3 and so on) that will insert a new worksheet (and name it unique based on information in cell A1), and then have it populate various cells on the new worksheet with information in cells A1-Y1. Can this be done? Can you help please?

Apr 19th, 2002, 02:48 AM	#2
robfo0 Board Regular Join Date: Feb 2002 Location: Stockton, California Posts: 281	Yes, you can do this. What would but buttons in cells z1, z2 etc be for? i was a little confused by that part. also, you said populate them to a new worksheet, is this what you mean, you want them in a new worksheet in the same workbook, or did you mean a new workBOOK? Here is a bit of code assuming you did mean worksheet. Sub CopyStuff() Dim CurrSheet As String Dim NewSheet As String CurrSheet = ActiveSheet.Name Sheets.Add ActiveSheet.Name = Sheets(CurrSheet).Range("A1").Value NewSheet = ActiveSheet.Name 'this copies range a1 from the first sheet to a1 in the new one. Repeat as needed Sheets(CurrSheet).Range("A1").Copy Destination:=Sheets(NewSheet).Range("A1") End Sub im not sure how familiar you are with excel, but heres how you get this into your workbook: -Press Alt+F11 in excel to active the VBA screen. -on the menu, go to Insert->Module -double click the new created module at the left (probably called module1) -paste this code -press Alt+Q to exit VBA now you can run the code by going to Tools->Macros->Macros... then select the "CopyStuff" macro to run it, OR you can create a button, then link it to CopyStuff when the link screen comes up hope that helps

Apr 19th, 2002, 05:50 PM	#3
boydr Board Regular Join Date: Apr 2002 Location: Arkansas Posts: 157	I think that might work. I'll try it and get back to you. Thanks for your help. BTW. I did want a new worksheet in the same workbook. I wanted a command button that would invoke the macro on demand on an as needed basis. For example, If I determine that the data in A1 thru Y1 is the data I want, then I want a command button in Z1 that I can click on and have it fill out a new worksheet with the data contained in cells A1 thru Y1. [ This Message was edited by: boydr on 2002-04-19 16:53 ]

Apr 20th, 2002, 07:18 AM	#4
boydr Board Regular Join Date: Apr 2002 Location: Arkansas Posts: 157	Robfo0, Thank you. The macro script works great with a little modification. I do have one problem. Some of the cells I'm copying have formula's in them, and when I copy them, it only copies the formula. Can I force it to take only the value and not the formula? [ This Message was edited by: boydr on 2002-04-20 06:27 ]