One way without any SELECTIONS.

Sub copyrangetonewsheetandname()
With Sheets("Quote Form")
newname = .Range("h10")
.Range(.Range("a1"), .Range("a1").SpecialCells(xlLastCell)).Copy
End With
Sheets.Add after:=Sheets(Sheets.Count)
With ActiveSheet
.Paste
.Name = newname
.Range("a1").Select
End With
Application.CutCopyMode = False
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Dorian C. Chalom" wrote in message
...
Hi Per;

Finally got back to this and I got it work except for one issue...
How do I determine the ame of the new sheet I add. I cannot always be
certain it will be a certain name.
Here is the Macro I ended up using.
I can not copy the whole spreadsheet because it gave me an error so I had
to do a range.
If you can clean this up at all please feel free...

Thank you.

Sub Macro3()
'
' Macro3 Macro
' Macro recorded 10/24/2009 by D Chalom
'

'
Sheets("100634").Select
Sheets.Add after:=Sheets(Sheets.Count)
Sheets("Quote Form").Select
Range("A1").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Copy
Sheets("Sheet3").Select
ActiveSheet.Paste
Sheets("Sheet3").Select
Sheets("Sheet3").Name = Worksheets("Quote Form").Range("H10").Value
End Sub

"Per Jessen" wrote in message
...
Hi

Try this:

Sub Macro1()
Dim newSh As Worksheet
Dim orgSh As Worksheet

Set orgSh = Worksheets("Sheet1")
Set newSh = Sheets("Sheet1").Copy(After:=Sheets(Sheets.Count))
newSh.Name = orgSh.Range("A1").Value
End Sub

Regards,
Per

"Dorian C. Chalom" skrev i meddelelsen
...
I am trying to create a macro that would copy a sheet within the same
workbook and rename it with the value from a cell on the sheet it copied
it from. Please help...

Thank you...