Ok, after few round testing, I have a better understand of this method now. The CopyWorkSheet actually does not copy the source worksheet to current worksheet (Self) but instead it append a new worksheet (duplicate of the source worksheet) to the workbook. As both the source and destination worksheet are in the same workbook, we must set ReplaceDuplicateName to true i.e. use a different name for the new worksheet appended.
We can omit the ReplaceDuplicateName (hence default to false)
if the source worksheet is in another workbook and there is no worksheet having the same name in the destination workbook.
Nevertherless, I think the error message 'SheetX is not a valid worksheet name' is misleading. May be 'SheetX already exists' would be better.