Don;

Sorry...
Can I have the formatting also on the copy?

Thank you...


"Don Guillett" wrote in message
...
Should do it

Option Explicit
Sub copyrangetonewsheetandnameValues()
Dim source As Worksheet
Dim la As String
Set source = Sheets("Quote Form")
Sheets.Add After:=Sheets(Sheets.Count)
With ActiveSheet
la = source.Cells.Find(What:="*", After:=[A1], _
SearchDirection:=xlPrevious).Address
'MsgBox la
.Range("a1:" & la).Value = source.Range("a1:" & la).Value
.Name = source.Range("h10")
End With
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Don Guillett" wrote in message
...
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...