Macro help - please!

[Guest]

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

 

[Guest]

One way:

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

 

[Damon Longworth]

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

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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