Copy a sheet and rename it


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

Advertisements

Joined
Sep 20, 2009
Messages
47
Reaction score
2
You can just RECORD the macro following those steps and edit the macro. This is he best way to learn to write macros.
 
P

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
 
D

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
 
D

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
 
D

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

Advertisements

D

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
 
Ad

Advertisements

D

Dorian C. Chalom

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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top