Copy and Remame Sheet in active Workbook

[Bjoern]

Hello,

I found many examples how to copy a sheet form one workbook to an other
in this newsgroup. But I want to copy a sheet (including all Data,
formating an formulas) and paste in with a new name to the same
(active) workbook.

Can you tell me how to do this in vba?

regards
Bjoern

 

[Don Guillett]

You could have recorded a manual copy (right click sheet tab>copy>etc)
Sub Macro7()
'
' Macro7 Macro
' Macro recorded 9/22/2007 by Donald B. Guillett
'

'
Sheets("Sheet20").Select
Sheets("Sheet20").Copy After:=Sheets(23)
Sheets("Sheet20 (2)").Select
Sheets("Sheet20 (2)").Name = "renamed"
End Sub

cleaned up

Sub copysheetandrename()
Sheets("Sheet20").Copy After:=Sheets(sheets.count)
ActiveSheet.Name = "renamed"
End Sub

 

[Guest]

hi,
somthing lilke this should do it.
Sheets("SomeSheet").select
Cells.Copy
Sheets.Add
ActiveSheet.Paste
Sheets("Sheet2").Name = "New name"

note: the new sheet will take the number of the next sheet number by default
untill you give it a name. even after giving it a name, you can still
reference the sheet by it's number in vb code. deleting sheets does not shift
numbers instead you have a gap in the sheet numbering. this can get tricky.

Regards
FSt1

 

[Gord Dibben]

Just don't run the macro twice or it will error out because you already have a
sheet named "renamed".

You may want to trap for such an occurence.

Sub copysheetandrename()
Dim n
On Error GoTo trap
n = 1
ActiveSheet.Copy After:=Sheets(Sheets.Count)
ActiveSheet.Name = "renamed" & n
Exit Sub
trap: If n = "" Then n = 0
n = n + 1
Resume
End Sub


Gord Dibben MS Excel MVP

 

[Bjoern]

You could have recorded a manual copy (right click sheet tab>copy>etc)

Oh, you are right. Sorry I'm not accustomed to "record" my scripts so I
haven't thought about this possibility. I will do better next time.

thanks for your help
Bjoern

 

[Bjoern]

Hello and thank you very much also about the extra hint.

regards
Bjoern

 

[Bjoern]

Hello and thank you, too.

I will create a sheet for every name out of a list of persons and I will
name the sheets like the Persons. But of cause you are right I have to
include something if two people have the same name. And I thing it will
be inspired by you're code snipe. Happily this part will be easy because
I sort the List before Creating the Sheets .

regards
Bjoern

 

[Don Guillett]

Try this from your list on sheet1 starting at a2
Sub findc1()
With Sheets("sheet1")
On Error GoTo nono
For i = .Cells(Rows.Count, "a").End(xlUp).Row To 2 Step -1
x = .Columns(1).Find(.Cells(i, 1), after:=.Cells(i, 1), _
SearchDirection:=xlPrevious).Row
If x > i Then
Sheets.Add.Name = .Cells(i, 1) & "1"
Else
Sheets.Add.Name = .Cells(i, 1)
End If
Next
nono:
End With
End Sub