Having a Subscript issue on Worksheet rename code

On the following code, I get to the line:

Please Login or Register  to view this content.

And then I get the error Subscript out of range. Can anyone identify the issue? Is it a problem with the rename of the sheet or the syntax?

Please Login or Register  to view this content.

It ran through two with this fix, but then on the third, it gave the same subscript out of range error on the With "wbNew.Worksheets("CDM PACKAGING") line. I'm going to try to find the file it attempted to modify to see if there are any issues within there that could have caused this.

It actually stops at the second. It writes the "ID Card Required" line and then gives the error, but in the resulting file, the two ranges are replaced.

It does not close the second workbook however so it doesn't seem like it gets to the wbNew.Close True line.

I have attached the sample after operation, and another example of before. The code is stopping on the "Sample", and "Sample2" is the sample before the code goes through.

I am running it from a blank workbook.

Sample2 works fine for me as well, it is getting hung up at the With statement in Sample1. It is a large directory, so there may be multiple issues similar to this. I am unsure of why it doesn't work for sample 1. It will just get stuck on this and will not close the workbook, so I'm assuming it isn't going all the way through for that part specific.

I attached a "virgin" sample.