Copy and rename a sheet

[Ticotion]

Hi

I'm trying to copy the active sheet via VBA and renaming it according to a
cell value found in a master data sheet. The sheet that is copied is to be
placed after the originalsheet (orgsh). I use the following code:

Set orgSh = ActiveSheet
Set newSh = ActiveSheet.Copy(After:=ActiveSheet)
newSh.Name = "Week" & " " & Sheets("Stamdata").Range("A4").Value

But I get an error referring to line 2.

Can you help?

Thank you

Ticotion

 

[Kruijf]

I use this code in some of my Workbooks.

Sub Copy
ActiveSheet.Copy After:=ActiveSheet
ActiveSheet.Name = Range("$A$1")

First line says that the active sheet should be copy and placed after the
activesheet. Then the second line of code will change the name in what is in
cell A1. I made a module and placed this lines in it. You can then make a
button or what ever you want.

Hope you can use it.

Greets Tom

 

[Ticotion]

That worked.

Thank you

I use this code in some of my Workbooks.

Sub Copy
ActiveSheet.Copy After:=ActiveSheet
ActiveSheet.Name = Range("$A$1")

First line says that the active sheet should be copy and placed after the
activesheet. Then the second line of code will change the name in what is in
cell A1. I made a module and placed this lines in it. You can then make a
button or what ever you want.

Hope you can use it.

Greets Tom