Copy a sheet and rename it

  • Thread starter Thread starter Dorian C. Chalom
  • Start date Start date
D

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...
 
You can just RECORD the macro following those steps and edit the macro. This is he best way to learn to write macros.
 
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
 
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
 
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;

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

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

Thank you...


Don Guillett said:
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)
Don Guillett said:
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)
Click to expand...
Click to expand...
 
Back
Top