copy row 2 of any wsheet to the new crated wsheet

[celio3c]

Hi there!
I have the code bellow that allow me to create and name the new wsheet,
now i need to copy what is in the row 2 of the prior wsheet or any sheet,
because all sheets have the same header.

apreciate all the help.

thanks

celio3c


Public Sub newWsheet()
Dim Message, Title, Default, MyValue
Message = "Enter a name for the new Worksheet" ' Set prompt.
Title = "Add New sheet Input Box" '
Set title.
Default = "Give me a Name" '
Set default.
MyValue = InputBox(Message, Title, Default) '
'Display message, title, and default value.

Set NewSheet = Worksheets.Add
NewSheet.Name = MyValue
cmbWsheet.AddItem MyValue

End Sub

 

[Socko]

Dim samplerow As Range
Set samplerow = ActiveSheet.Rows(2)

'Display message, title, and default value.
Dim Message, Title, Default, MyValue
Message = "Enter a name for the new Worksheet" '
Set prompt.
Title = "Add New sheet Input
Box" 'Set title.
Default = "Give me a
Name" 'Set default.
MyValue = InputBox(Message, Title, Default) '


Set NewSheet = Worksheets.Add
NewSheet.Name = MyValue
'cmbWsheet.AddItem MyValue

NewSheet.Activate
NewSheet.Cells(2, 1).Select

samplerow.Copy
ActiveSheet.Paste

ActiveSheet.Cells(1, 1).Activate

I hope this helps...

Selva V Pasupathy
For more on Excel, VBA, & other Resources
Please visit: http://socko.wordpress.com

 

[Gord Dibben]

Sub addsheet()
Set oldsheet = ActiveSheet
MyValue = InputBox("Enter a name", "Add New Sheet Input Box")
Set NewSheet = Worksheets.Add
NewSheet.Name = MyValue
oldsheet.Rows("2:2").Copy Destination:=ActiveSheet.Range("A2")
End Sub


Gord Dibben MS Excel MVP

 

[Gord Dibben]

Shorter yet.

Sub addsheet()
Set oldsheet = ActiveSheet
MyValue = InputBox("Enter a name", "Add New Sheet Input Box")
Worksheets.Add.Name = MyValue
oldsheet.Rows("2:2").Copy Destination:=ActiveSheet.Range("A2")
End Sub


Gord

 

[celio3c]

Thanks Socko and Gord

it did worked, perhaps i need a litle more to cover the situation, what if
the name entered already exist, i'm getting a error on that.

 

[Gord Dibben]

Sub addsheet()
Set oldsheet = ActiveSheet
On Error GoTo oops
MyValue = InputBox("Enter a name", "Add New Sheet Input Box")
Worksheets.Add.Name = MyValue
oldsheet.Rows("2:2").Copy Destination:=ActiveSheet.Range("A2")
Exit Sub
oops:
MsgBox "Sheet name already exists. Try again"
End Sub


Gord

 

[celio3c]

once more thank you very much, nicely done gorb.