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

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
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"Bjoern" <quasselbabbel@yahoo_delete_this.de> wrote in message
news:OYm4dyV$(E-Mail Removed)...
> 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

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

"Bjoern" wrote:

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

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

On Sat, 22 Sep 2007 16:22:07 -0500, "Don Guillett" <(E-Mail Removed)>
wrote:

>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

Don Guillett schrieb:
> 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

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

regards
Bjoern

FSt1 schrieb:
> 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
>
> "Bjoern" wrote:
>
>> 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
>>

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

Gord Dibben schrieb:
> 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
>
> On Sat, 22 Sep 2007 16:22:07 -0500, "Don Guillett" <(E-Mail Removed)>
> wrote:
>
>> 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
>

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"Bjoern" <quasselbabbel@yahoo_delete_this.de> wrote in message
news:ObFPPgW$(E-Mail Removed)...
> 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
>
> Gord Dibben schrieb:
>> 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
>>
>> On Sat, 22 Sep 2007 16:22:07 -0500, "Don Guillett"
>> <(E-Mail Removed)>
>> wrote:
>>
>>> 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
>>