Copy a worksheet an rename using VBA

[lhernandez]

Hello!
I am currently trying to copy a worksheet and rename the copied worksheet. Based on my other code this could be performed multiple times so there is no way to know the exact name of the worksheet i will be renaming.
I am using the following:

Set ws = Sheets("Sheet1")
ws.Select
ws.Copy after:=Sheets(3)
ws.Name = "Test"

What ends up happening is "Sheet1" is copied after "Sheet3", which at this point would be names "Sheet1 (2)", and the original "Sheet1" is renamed to "Test"...is there a way to set the copied sheet, "Sheet1 (2)" to be renamed as "Test"?

Thanks for your help in advance

 

[VoG]

Try

Sheets("Sheet1").Copy after:=Sheets("Sheet3")
ActiveSheet.Name = "Test"

 

[lhernandez]

Thanks! exactly what i needed!

 

[Norie]

Try this.

Set ws = Sheets("Sheet1")
 
ws.Copy After:=Sheets("Sheet3")
 
Set wsNew = Sheets(Sheets("Sheet3").Index+1)
 
wsNew.Name  = "Test"

 

[xzubv]

Norie, you rock

 

[hart143]

Hi, what does

 Set wsNew = Sheets(Sheets("Sheet3").Index+1)

mean or do? I follow the rest of the sample but this line I do not understand.

Try this.

Set ws = Sheets("Sheet1")
 
ws.Copy After:=Sheets("Sheet3")
 
Set wsNew = Sheets(Sheets("Sheet3").Index+1)
 
wsNew.Name  = "Test"

 

[Norie]

It sets wsNew to refer to the sheet that follows Sheet3, i.e. the sheet that has been copied after Sheet3.