Copy and Remame Sheet in active Workbook

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Bjoern - 22 Sep 2007 22:07 GMT

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

Reply to this Message

Don Guillett - 22 Sep 2007 22:22 GMT

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

Signature

Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com

> Hello,
>
[quoted text clipped - 7 lines]
> regards
> Bjoern

Reply to this Message

Gord Dibben - 22 Sep 2007 23:00 GMT

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

>You could have recorded a manual copy (right click sheet tab>copy>etc)
>Sub Macro7()
[quoted text clipped - 16 lines]
> ActiveSheet.Name = "renamed"
>End Sub

Reply to this Message

Bjoern - 22 Sep 2007 23:29 GMT

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".
[quoted text clipped - 35 lines]
>> ActiveSheet.Name = "renamed"
>> End Sub

Reply to this Message

Don Guillett - 23 Sep 2007 14:49 GMT

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

Signature

Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com

> Hello and thank you, too.
>
[quoted text clipped - 52 lines]
>>> ActiveSheet.Name = "renamed"
>>> End Sub

Reply to this Message

Bjoern - 22 Sep 2007 23:14 GMT

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

Reply to this Message

FSt1 - 22 Sep 2007 22:30 GMT

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

> Hello,
>
[quoted text clipped - 7 lines]
> regards
> Bjoern

Reply to this Message

Bjoern - 22 Sep 2007 23:16 GMT

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

regards
Bjoern

FSt1 schrieb:

> hi,
> somthing lilke this should do it.
[quoted text clipped - 23 lines]
>> regards
>> Bjoern

Reply to this Message