Copy a sheet and rename it

[Dorian C. Chalom]

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...

 

[venkat1926]

You can just RECORD the macro following those steps and edit the macro. This is he best way to learn to write macros.

 

[Per Jessen]

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]

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

 

[Don Guillett]

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

 

[Dorian C. Chalom]

Don;

OK Close....I forgot to mention one thing...They want to Paste Values
because they want to keep the copy for hostorical purposes. If I must
figure this part on my own I will...

Thank you...

 

[Don Guillett]

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

 

[Dorian C. Chalom]

Don;

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

Thank you...

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
(e-mail address removed)

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
(e-mail address removed)