Copy and rename a sheet
 

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

Copy and rename a sheet
 

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" wrote:

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

Copy and rename a sheet
 

That worked.

Thank you

"Kruijf" wrote:

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" wrote:

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