Copy WS and rename in VBA

[Iulika]

Hello,

I am not familiar with VBA codes, I am just recording them.
I have this Excel document where I am copying sheet1 in a new ws at the end of the existing worksheets in the same document and I am formating it the way I want with VBA.
After formating it, I am naming the ws "Current". That is working fine, however if the name "Current" already exists, the macro gives me an error. How can I name the new created ws "New Current" only if "Current" already exists?

Can anyone help me with the VBA code for this?
Thank you so much.

[Andy Pope]

You can check to see if the sheet already exists.

Please Login or Register  to view this content.

Ideally you would use a function to test for the sheet.
So more like this.

Please Login or Register  to view this content.

[Iulika]

Hello,

I am not familiar with VBA codes, I am just recording them.
I have this Excel document where I am copying sheet1 in a new ws at the end of the existing worksheets in the same document and I am formating it the way I want with VBA.
After formating it, I am naming the ws "Current". That is working fine, however if the name "Current" already exists, the macro gives me an error. How can I name the new created ws "New Current" only if "Current" already exists?

Can anyone help me with the VBA code for this?
Thank you so much.

Hi Andy,
Thanks for the help. However, I am unable to use this code.

I think it has to do with the fact that I am already copying the werksheet that I want at the end of the existing worksheets and I am naming it. So the macro gives me the error, but doesn't take me to the next step "On Error resume Next".
Here is how my codes are:

Sheet1.Select
Sheet1.Copy after:=Sheet7

When I copy sheet1, excel creates sheet 8 named "Sheet1 (2)".

Then I name sheet8 with this code:

With ActiveSheet.Select
ActiveSheet.Name = "Current"
End With

So if I already have a Sheet named "Current", how can I change the name of the new one to New Current?

Please help.
Thank you.

[Andy Pope]

Please Login or Register  to view this content.