Trying to copy a set of sheets and rename them based on user input

Hello,

So I have a span of worksheets within a workbook, each name is in the format of "20 _______" except the first of the set, which is just "20"
I want to copy this set of sheets, in order, and rename them based on the operation number the user puts in - so the next set should be 30.

Currently, I get all the worksheets copied to the end of the sheets, which is correct, and the first sheet copied is correctly renamed, but the other sheets are not remained - they just get assigned "20 ______ (2)"

I have also tried using

Please Login or Register  to view this content.

instead of

Please Login or Register  to view this content.

in the For Each/If loop, but that didn't work

Here's the code:

Please Login or Register  to view this content.

Originally Posted by JLGWhiz

try this

Please Login or Register  to view this content.

So this works well for every copied sheet except for the one titled just "20".

I tried writing an extra IF statement specifically to copy and rename the 20 page to opNum - this worked, but then I had two copies of 20, so I tried to add another condition to the original block that would check if ws.Name <> "20", but this failed to work.

I'm curious why this fails to rename "20" to opNum. Any idea?

How about

Please Login or Register  to view this content.

Yeah, this seems to work. My guess is you can't name a worksheet with an integer value, but concatenating the integer with a string coerces the type to string, which is why the one that was just =opNum didn't work. So the Variant type allows you to set just the opNum as the sheet name.

You can name a sheet using an integer, but the reason for using

Please Login or Register  to view this content.

is to remove the

Please Login or Register  to view this content.

that you had, as that line simply masks any errors that you were getting.

Cool, thank you. I know next to nothing about VBA so just trying to piece things together here.

Glad to help & thanks for the feedback